11

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!

Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
buri kuri
  • 429
  • 3
  • 7
  • 19
  • 1
    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 Apr 30 '12 at 20:51

2 Answers2

20

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
Doug Glancy
  • 27,214
  • 6
  • 67
  • 115
  • 1
    I don't know. Since your tags included `excel-vba` I gave you that code. Sometime people use "vbscript" and "vba" interchangeably. – Doug Glancy Apr 30 '12 at 20:31
  • 2
    @buri kuri The code would require significant adapting for `vbscript`, ie to automate Excel, remove explicit dimensioning of variables, add constants for `xlWhole` etc. You need to be more specific as to what you want – brettdj May 01 '12 at 03:42
  • Hi friends, I solved the find problem by using the solution of Doug and here is the another problem. When I try to seach "1/1/2011" from the A column, it brings "11/1/2011" row number which I don't want to match like this. How can I maintain exact matching for characters? Btw I used vbscript and here is the line --> Set FoundCell = objSheet.Range("A:A").Find("1/1/2011") – buri kuri May 01 '12 at 16:34
  • You need to get the `LookAt:=xlWhole` argument in there. That's what @brettdj was referring to by "constants." This is a guess, but try `objSheet.Range("A:A").Find("1/1/2011",,,1,,,,,)`. I think this will work because `LookAt` is the 4th argument to Find and 1 is the numeric (constant) equivalent of xlWhole. – Doug Glancy May 01 '12 at 17:37
  • This is the answer of my question --> objSheet.Range("A:A").Find("1/1/2011",,,1) – buri kuri May 01 '12 at 20:33
  • How can I find the last instance? – Mark Romano Oct 26 '16 at 20:16
-2

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
Ian Laurin
  • 1