|
Find method without "With / Set" in a For...each: error after 1st run
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 |
Find method without "With / Set" in a For...each: error after 1st
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 -- HTH... Jim Thomlinson "jo77" wrote: 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 |
Find method without "With / Set" in a For...each: error after
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) -- HTH... Jim Thomlinson "Joel" wrote: 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 "jo77" wrote: 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 |
| All times are GMT +1. The time now is 02:37 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com