Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Excel VBA to convert column to number. Use as get_column_number
'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