Excel VBA: getRowNum() and getColNum()

Here are two functions that I will be using in an upcoming sheet, I want to explain them briefly and add them to my small library (file is downloadable at the end of this post).

The names hint at the purpose: given a value to match, they will return the number of the row or column the value was found in, or zero if it wasn’t found at all. The concept is similar to the “vlookup” formula, but performance for these two functions is terrible by comparison and for that reason I don’t recommend using them repetitively in a loop or with large columns of data. I maintain several files with built-in functionality where most of the user input comes from cells in a sheet but I can’t always guarantee the position of those cells – that’s where these two come in handy. Instead of hard-coding the cell address, these functions can find the address by matching the value in that cell which allows you to modify the user-facing sheet without having to update code every time; it also makes the sheet somewhat tolerant of being rearranged by the user, but you need to be careful in case it’s been mangled in such a way that the inputs are found but are totally bogus.

getRowNum()

Function getRowNum(findValue As Variant, Optional sheetName As String, Optional colNum As Long) As Long
'by Elliot 8/9/20 www.elliotmade.com
'this function will return the row number in the first (or specified) column that matches the "findValue" parameter
'this is similar to vlookup, but not as fast or performant
'an example use is to get an input from a sheet where you can't guarantee the position of the cell
'combine this with getColNum() to find a cell value where a known column heading and row label intersect
'****** this requires the "getlastrow" function *******

Dim i As Long
Dim lastRow As Long

Dim j As Long
If sheetName <> "" Then
    For j = 1 To Worksheets.Count
        If Worksheets(j).Name = sheetName Then GoTo sheetOK
    Next j
    GoTo abort 'specified sheet name was not found
sheetOK:
End If

'default values for optional parameters
If sheetName = "" Then sheetName = ActiveSheet.Name
If colNum = 0 Then colNum = 1

lastRow = getLastRow(sheetName, colNum)

For i = 1 To lastRow
    If Worksheets(sheetName).Cells(i, colNum).Value2 = findValue Then
        getRowNum = i
        GoTo found
    End If
Next i

found:
abort:

End Function

getColNum()

Function getColNum(findValue As Variant, Optional sheetName As String, Optional rowNum As Long) As Long
'by Elliot 8/9/20 www.elliotmade.com
'this function will return the column number in the first (or specified) column that matches the "findValue" parameter
'this is similar to vlookup, but inverted not as fast or performant
'an example use is to get an input from a sheet where you can't guarantee the position of the cell
'combine this with getRowNum() to find a cell value where a known column heading and row label intersect
'****** this requires the "getLastCol" function *******

Dim i As Long
Dim lastCol As Long

Dim j As Long
If sheetName <> "" Then
    For j = 1 To Worksheets.Count
        If Worksheets(j).Name = sheetName Then GoTo sheetOK
    Next j
    GoTo abort 'specified sheet name was not found
sheetOK:
End If

'default values for optional parameters
If sheetName = "" Then sheetName = ActiveSheet.Name
If rowNum = 0 Then rowNum = 1

lastCol = getLastCol(sheetName, rowNum)

For i = 1 To lastCol
    If Worksheets(sheetName).Cells(rowNum, i).Value2 = findValue Then
        getColNum = i
        GoTo found
    End If
Next i

found:
abort:

End Function

The usage for both of these is simple, just give them a value to match, and if you want to search in a different row/column or a different sheet use the optional parameters. Note that these both rely on two functions I posted previously – getLastRow() and getLastCol(), both of which are included in the file below as well.