0

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.

Patrick M
  • 10,547
  • 9
  • 68
  • 101
Terry S
  • 23
  • 1
  • 6
  • what is your variable for `Sht`? Likely cause of your issues is that your variable `Sht` is `Set Sht = Workbooks("SomeWorkbook").Sheets("SomeWorksheet")`. If that is true, then the correct syntax is simply `Sht.Cells(Rw, 1) <> 1` – Daniel Oct 24 '14 at 22:19
  • You can't really chain variables like that. If `Sht` is already parented to `Wbk` you don't need to include `Wbk` in the variable call. Using `Wbk.Sht.Cells(...` is invalid, but just `Sht.Cells(...` would be valid. To use just the `Wbk` variable would be `Wbk.Sheets("SheetName").Cells(...` – tigeravatar Oct 24 '14 at 22:20
  • @tigeravatar I posted an answer, but if you wish to post your comment as answer, I'll delete mine :) – L42 Oct 25 '14 at 01:10

1 Answers1

1

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) <> ""
Community
  • 1
  • 1
L42
  • 19,427
  • 11
  • 44
  • 68
  • Thanks for the answers. I made a simple change and it works now. This is in a called routine and Wbk is passed to this routine The line of code to set Wbk is : Set Wbk = ActiveWorkbook Sht was declared as Worksheet and Set Sht = Sheets(1). This generated the error. I changed this to Y=1 and the line that got the error is now: If Wbk.Sheets(Y).Cells(Rw, 1) <> "" Then. It works now. – Terry S Oct 27 '14 at 14:10