CODEWITHSUNDEEP
vbaexcel
Terry S
Terry S

Reputation: 23

error when using variable set as active workbook

I have a line of code that seems to generate an error no matter how it is written. In this case:

Set Wbk = ActiveWorkbook

Wbk.Sht.Cells(Rw, 1) <> ""

I get error 438

If I change the last line to

Workbooks(WbName).Sht.Cells(Rw, 1) <> ""

where WbName is the active workbook name I get error 9.

If I scrap the workbook beginning and just Sht which is set to the worksheet it works.

Upvotes: 0

Views: 1174

Answers (1)

L42
L42

Reputation: 19727

This link should tell you a lot of things.
As for your question, let's elaborate what tigeravatar commented.

If you set your workbook like this:

Set Wbk = Activeworkbook

And your worksheet like this:

Set Sht = Activeworkbook.Sheets("Sheet1")

These 2 variables becomes independent of each other.
As what tigeravatar said, you cannot chain variables like that.
Or simply put, that violates the Excel Object Hierarchy.
There are times though that you need to use variables in reference to another object.
You can try this to access Sht through Wbk:

If Wbk.Sheets(Sht.Name).Cells(Rw, 1) <> "" Then

Or if you want to access the workbook using Sht:

Sht.Parent.Sheets("Sheet1").Cells(Rw, 1) <> ""

Or something like this:

Sht.Parent.Parent.Worksbooks(Wbk.Name).Sheets(Sht.Name).Cells(Rw, 1) <> ""

Upvotes: 1

Related Questions