How to find the row number of a specific value in Excel using vbscript

Question

I have an open Excel file and using VB Script, I need to search only column "A" in the Excel sheet until it matches a text string. When the script finds that match, I would like to see the row number of the cell where the match was found. Thanks for your helps in advance!

Answer 1

This is VBA to find the first instance of "test2" in column A of the activesheet. You can adjust the string and worksheet accord to your needs. It only counts as a match if the whole cell matches, e.g., "test2222" won't match. If you want it to, remove the , lookat:=xlWhole bit:

Sub FindFirstInstance()
Const WHAT_TO_FIND As String = "test2"
Dim ws As Excel.Worksheet
Dim FoundCell As Excel.Range

Set ws = ActiveSheet
Set FoundCell = ws.Range("A:A").Find(what:=WHAT_TO_FIND, lookat:=xlWhole)
If Not FoundCell Is Nothing Then
    MsgBox (WHAT_TO_FIND & " found in row: " & FoundCell.Row)
Else
    MsgBox (WHAT_TO_FIND & " not found")
End If
End Sub