Perform for-each and find actions in other not active workbooks

[npanag]

I have the following problem: I have Wbk1.xlsx activated and I want some code to be executed on Wbk2.xlsx :

For Each rg1 In Workbooks("Wbk2.xlsx").Worksheets("Sheet1").Range(Cells(nPostingDate_Row, 1), Cells(nPostingDate_Row, nFinal_Column))
nPostingDate_Row is 12 in the example and nFinal_Column is 21.

It responds with application-defined or object error.

I noticed that if I activate Wbk2.xlsx, then it's working.
But I want to have one workbook activated and perform for-each and search actions to other workbooks.

For example while I have Wbk1.xlsx activated, I want the following code fraction to run successfully:

Set rgf1 = Workbooks("Wbk2.xlsx")Sheets("Sheet1").Range(Cells(1, nDebit_Column), Cells(nFinal_Row, nDebit_Column)).Find(What:=abs_Amount)
if rgf1 is nothing then
'actions set 1
else
'actions set 2
endif

This kind of search has also an application error.

What am I missing? Can't I use full workbook definitions in for-each or find actions?

Thank you

 

[Norie]

You don't have workbook/worksheet references for Cells here.

Cells(nPostingDate_Row, 1), Cells(nPostingDate_Row, nFinal_Column)

I would suggest you introduce another range variable, let's call it rngData, set it like this,

With Workbooks("Wbk2.xlsx").Worksheets("Sheet1")
    Set rngData = .Range(.Cells(nPostingDate_Row, 1), .Cells(nPostingDate_Row, nFinal_Column))
End With

and then loop like this.

For Each rg1 In rngData.Cells
    ' do stuff
Next rg1

 

[npanag]

Yes, you are right. The "with" case you mention and the ".Cells" reference you use solve the issues.

I never thought that the Cells inside the range parenthesis need also a reference.

Thank you very much for your answer.

Nick