Last active
August 29, 2015 14:17
-
-
Save jaykilleen/294d41c3b774bc88f82d to your computer and use it in GitHub Desktop.
Excel VBA to convert column to number. Use as get_column_number
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
'Just copy the function below into a new module | |
'I usually call this module 'functions' | |
'You can then call anywhere in your scripts `get_column_number("header column name", 15, TRUE) | |
'This will look in row 1 (usually where your headers are on a table) for a value and return the column number | |
'If your headers start on a different row (ie you have slicers above your table) you can pass in the row number | |
`This will match a partial word by default, pass in TRUE if you want to match by the whole word | |
`checkout https://gist.github.com/jaykilleen/892d0f0eed87b9f8e6b0 for other functions that I like to add into my 'functions' module. | |
Function get_column_number(sheet As Worksheet, value As String, Optional row As Variant, Optional whole As Boolean) As Integer | |
Dim looks As String | |
If IsMissing(row) Then | |
row = 1 | |
End If | |
If whole = False Then | |
get_column_number = Sheets(sheet.Name).Cells(row, 1).EntireRow.Find(What:=value, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False).column | |
Else | |
get_column_number = Sheets(sheet.Name).Cells(row, 1).EntireRow.Find(What:=value, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False).column | |
End If | |
End Function |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment