A simple function to return the row number of a table where a value is matched in a column. Like a VLOOKUP or INDEX MATCH for VBA.
me@jaykilleen.com wrote this almost 8 years ago and it was last updated almost 8 years ago.
Just copy the function below into a new module. I usually call this module 'functions'
You can then call anywhere in your scripts `get_row_number("dummy text", 1)
The integer '1' will tell this to do a ctrl+f on column A. When it finds the first value it will return the row number of that value. Best used on columns that contain unique values otherwise it will reference the first value that it finds.
You might need to change the xlPart to xlWhole if you are going for an exact match.
You could also extend this function to pass in a boolean to look for whole or partial value and replace xlPart with the value passed in checkout https://gist.github.com/jaykilleen/892d0f0eed87b9f8e6b0 for other functions that I like to add into my 'functions' module.
Function get_row_number(
sheet As Worksheet,
value As Variant,
column As Integer
) As Integer
get_row_number = Sheets(sheet.Name).Cells(1, column).EntireColumn.Find(
What:=value,
LookIn:=xlValues,
LookAt:=xlPart,
SearchOrder:=xlByColumns,
SearchDirection:=xlNext,
MatchCase:=False
).row
End Function