CODEWITHSUNDEEP
excelvbscript
TriniCurry
TriniCurry

Reputation: 57

VBS FindNext method of range class failed

Hello just some Quick help on this subject with .Find method in

It always loop back to the same first cell it found , now with .findnext (first cell address) i get an error saying FindNext method of range class failed

Any help will be greatly appreciated here is what i have so far:

Set objXLApp = CreateObject("Excel.Application")
Set EffR = objXLApp.Workbooks.Open("C:\Users\Daniel\Desktop\March Eff Report.xlsx")
objXLApp.Application.Visible = True
objXLApp.DisplayAlerts= False 
Set CPS = EffR.Sheets(3)
Set MTDsum = EffR.Sheets(4)
Set DIMA1 = EffR.Sheets(5)
Set DIMA2 = EffR.Sheets(6)
Set CP2 = EffR.Sheets(7)
Set CP3 = EffR.Sheets(8)
Set AO500 = EffR.Sheets(9)
Set AO501 = EffR.Sheets(10)
DIMA1.Activate

findMe = "7001030025F" 
Set rSearch = DIMA1.Range("A1")
Set rFound = rSearch.Find(findMe)
adrFirst = rFound.Address
'wscript.echo ""&adrFirst&""
rSearch.FindNext(adrFirst)

Upvotes: 1

Views: 552

Answers (1)

brettdj
brettdj

Reputation: 55682

Several comments:

  • You are only searching a single cell, so I presume your code should be looking at a longer range (it can only find one match).
  • You need to cater for not finding a match.
  • Updated code below.

code

Set rsearch = DIMA1.Range("A1")

Set rfound = rsearch.Find(findMe)
If Not rfound is nothing Then
wscript.echo "found"
    adrFirst = rfound.Address
Do
Set rfound = rsearch.FindNext(rfound)
Loop While rfound.Address <> adrFirst
else
wscript.echo "not found"
End If

Upvotes: 1

Related Questions