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

11
votes

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!

2
excelvbscript
Show the code you have so far - easier to add to that than to guess whether you really mean VBA or vbscript. As Doug mentions, they are sometimes used to mean the same thing. – Tim Williams

2 Answers

20
votes

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
-2
votes

Thanks for the sample. Below it is in VBScript

Dim FSO, oExcel, oData, FoundCell, WHAT_TO_FIND, File_Path

WHAT_TO_FIND = "Report Summary"
File_Path = "\\[Server]\[Drive$]\[Folder]\Data.xls"

Set FSO = CreateObject("Scripting.FileSystemObject")
Set oExcel = CreateObject("Excel.Application")
Set oData = oExcel.Workbooks.Open(File_Path)

Set FoundCell = oData.Worksheets("Sheet1").Range("A4:A20000").Find(WHAT_TO_FIND)
If Not FoundCell Is Nothing Then
  MsgBox (WHAT_TO_FIND & " found in row: " & FoundCell.Row)
Else
  MsgBox (WHAT_TO_FIND & " not found")
End If

Set File_Path = nothing
Set WHAT_TO_FIND = nothing
Set FoundCell = nothing
Set oData = Nothing
Set oExcel = Nothing
Set FSO = Nothing