Find method without "With / Set" in a For...each: error after 1st run

[jo77]

Hello everyone,

I need to write a for...each routine including the find method.
I'm not very familiar with the with / set declarations and therefore,
tried to reproduce the "Find" method as recorded by the macro
recorder, which seems easier to understand to me.
The routine works correctly during the 1st run, but stucks during the
2nd run with following error: "Run-time error 91 - Object variable or
With block variable not set". VBA reports the following line as error:

Selection.Find(what:=Valor, LookIn:=xlValues,
lookat:=xlWhole).Activate

I would like, to activate the cell found.
Can anyone explain me why ? Is there any other possibility without
using the With / Set method ?

Thanks for any help & a great weekend.
Joël

Here the beginning of the For...Each routine:

Workbooks(pebeFile).Activate

For Each Cell In Workbooks(pebeFile).Worksheets(1).Range("A2:A65536")
Select Case Cells(CurrentRowpebe, p_ColDepotstelle)
Case ""
Exit For 'if cell empty, it means end of file is
reached
Case Else
Valor = Cells(CurrentRowpebe, p_ColValor)
Wahrung = Cells(CurrentRowpebe, p_ColWrg)
Holdingpebe = Cells(CurrentRowpebe, p_ColNom)
Bezeichnung = Cells(CurrentRowpebe, p_ColBezeichnung)

If CheckValuation = True Then
Pricepebe = Cells(CurrentRowpebe, p_ColBewKurs)
ValuationFWpebe = Cells(CurrentRowpebe,
p_ColBewWertFW)
FXRatepebe = Cells(CurrentRowpebe, p_ColBewDevKurs)
ValuationLWpebe = Cells(CurrentRowpebe,
p_ColBewWertLW)
ValCurrencypebe = Cells(CurrentRowpebe, p_ColWrg)
End If
If CheckInterest = True Then
InterestFWpebe = Cells(CurrentRowpebe,
p_ColMarchzinsFW)
InterestLWpebe = Cells(CurrentRowpebe,
p_ColMarchzinsLW)
End If

Cells(CurrentRowpebe, 52).Value = "PROCESSED"

'*******************
'SEARCH IN AISFILE *
'*******************

Workbooks(AISFile).Worksheets(1).Activate
Workbooks(AISFile).Worksheets(1).Range("A4:A65536").Select

'LINE CAUSING APPARENTLY THE PROBLEM
Selection.Find(what:=Valor, LookIn:=xlValues,
lookat:=xlWhole).Activate

If Selection Is Nothing Then
'Instrument not found in AISFile, report position as
mistake

HoldingAIS = 0
PriceAIS = 0
ValuationFWAIS = 0
FXRateAIS = 0
ValuationLWAIS = 0
ValCurrencyAIS = 0
......
Next Cell

 

[Joel]

Workbooks(AISFile).Worksheets(1).Activate
set SearchRange =
Workbooks(AISFile).Worksheets(1).Range("A4:A65536")

'LINE CAUSING APPARENTLY THE PROBLEM
set c = searchrange.Find(what:=Valor, LookIn:=xlValues,
lookat:=xlWhole).Activate

If c Is Nothing Then
'Instrument not found in AISFile, report position as
mistake

HoldingAIS = 0
PriceAIS = 0
ValuationFWAIS = 0
FXRateAIS = 0
ValuationLWAIS = 0
ValCurrencyAIS = 0

 

[Jim Thomlinson]

You can not safely use find code without using the set command. The issue you
will run into is if what you are looking for is not found then you are trying
to activate a cell that does not exist.

try this

dim rngFound as range
set rngfound = Selection.Find(what:=Valor, LookIn:=xlValues,
lookat:=xlWhole, MatchCase:=false) 'Note I added MatchCase

If rngFound Is Nothing Then
'Instrument not found in AISFile, report position as
mistake

HoldingAIS = 0
PriceAIS = 0
ValuationFWAIS = 0
FXRateAIS = 0
ValuationLWAIS = 0
ValCurrencyAIS = 0

 

[Jim Thomlinson]

You forgot to remove the .activate on your set

set c = searchrange.Find(what:=Valor, LookIn:=xlValues,
lookat:=xlWhole).Activate

should be

set c = searchrange.Find(what:=Valor, LookIn:=xlValues,
lookat:=xlWhole)