Seeking Help with CELLS Property in VBA

[USNA91]

Hello again...

Originally, I had the following block of code set up:

Sub UpdateWorksheets()

Dim RefSheet As Worksheet
   
    Application.ScreenUpdating = False
    Set RefSheet = Worksheets("Lookups and Calculations")
   
    UpdateTMSheet Sheet11, RefSheet.Range("E4").Value, RefSheet.Range("F4").Value, RefSheet.Range("I4")
    UpdateTMSheet Sheet12, RefSheet.Range("E5").Value, RefSheet.Range("F5").Value, RefSheet.Range("I5")
    UpdateTMSheet Sheet13, RefSheet.Range("E6").Value, RefSheet.Range("F6").Value, RefSheet.Range("I6")
    UpdateTMSheet Sheet14, RefSheet.Range("E7").Value, RefSheet.Range("F7").Value, RefSheet.Range("I7")
    UpdateTMSheet Sheet15, RefSheet.Range("E8").Value, RefSheet.Range("F8").Value, RefSheet.Range("I8")
    UpdateTMSheet Sheet16, RefSheet.Range("E9").Value, RefSheet.Range("F9").Value, RefSheet.Range("I9")
    UpdateTMSheet Sheet17, RefSheet.Range("E10").Value, RefSheet.Range("F10").Value, RefSheet.Range("I10")
    UpdateTMSheet Sheet18, RefSheet.Range("E11").Value, RefSheet.Range("F11").Value, RefSheet.Range("I11")
    UpdateTMSheet Sheet19, RefSheet.Range("E12").Value, RefSheet.Range("F12").Value, RefSheet.Range("I12")
    UpdateTMSheet Sheet20, RefSheet.Range("E13").Value, RefSheet.Range("F13").Value, RefSheet.Range("I13")
    UpdateTMSheet Sheet21, RefSheet.Range("E14").Value, RefSheet.Range("F14").Value, RefSheet.Range("I14")
    UpdateTMSheet Sheet22, RefSheet.Range("E15").Value, RefSheet.Range("F15").Value, RefSheet.Range("I15")
    UpdateTMSheet Sheet23, RefSheet.Range("E16").Value, RefSheet.Range("F16").Value, RefSheet.Range("I16")
    UpdateTMSheet Sheet24, RefSheet.Range("E17").Value, RefSheet.Range("F17").Value, RefSheet.Range("I17")
    UpdateTMSheet Sheet25, RefSheet.Range("E18").Value, RefSheet.Range("F18").Value, RefSheet.Range("I18")
    UpdateTMSheet Sheet26, RefSheet.Range("E19").Value, RefSheet.Range("F19").Value, RefSheet.Range("I19")
    UpdateTMSheet Sheet27, RefSheet.Range("E20").Value, RefSheet.Range("F20").Value, RefSheet.Range("I20")
    UpdateTMSheet Sheet28, RefSheet.Range("E21").Value, RefSheet.Range("F21").Value, RefSheet.Range("I21")
    UpdateTMSheet Sheet29, RefSheet.Range("E22").Value, RefSheet.Range("F22").Value, RefSheet.Range("I22")
    UpdateTMSheet Sheet30, RefSheet.Range("E23").Value, RefSheet.Range("F23").Value, RefSheet.Range("I23")
    UpdateTMSheet Sheet31, RefSheet.Range("E24").Value, RefSheet.Range("F24").Value, RefSheet.Range("I24")
    UpdateTMSheet Sheet32, RefSheet.Range("E25").Value, RefSheet.Range("F25").Value, RefSheet.Range("I25")
    UpdateTMSheet Sheet33, RefSheet.Range("E26").Value, RefSheet.Range("F26").Value, RefSheet.Range("I26")
    UpdateTMSheet Sheet34, RefSheet.Range("E27").Value, RefSheet.Range("F27").Value, RefSheet.Range("I27")
    UpdateTMSheet Sheet35, RefSheet.Range("E28").Value, RefSheet.Range("F28").Value, RefSheet.Range("I28")
    UpdateTMSheet Sheet36, RefSheet.Range("E29").Value, RefSheet.Range("F29").Value, RefSheet.Range("I29")
    UpdateTMSheet Sheet37, RefSheet.Range("E30").Value, RefSheet.Range("F30").Value, RefSheet.Range("I30")
    UpdateTMSheet Sheet38, RefSheet.Range("E31").Value, RefSheet.Range("F31").Value, RefSheet.Range("I31")
    UpdateTMSheet Sheet39, RefSheet.Range("E32").Value, RefSheet.Range("F32").Value, RefSheet.Range("I32")
    UpdateTMSheet Sheet40, RefSheet.Range("E33").Value, RefSheet.Range("F33").Value, RefSheet.Range("I33")
       
    Sheets("Setup and Update Status").Activate
    Application.ScreenUpdating = True

End Sub

Obviously not the most efficient bit of code...

So I began doing some research, and based on what I found I tried to replace the above with this:

Sub UpdateWorksheetsTest()

Dim RefSheet As Worksheet, _
    Sheet As Worksheet, _
    Counter As Integer, _
    RefRow As Integer

    Application.ScreenUpdating = False
   
    Set RefSheet = Worksheets("Lookups and Calculations")
 
    For Each Sheet In Worksheets

        Counter = Val(Right(Sheet.CodeName, Len(Sheet.CodeName) - 5))
        RefRow = Counter - 7

        If Counter >= 11 And Counter <= 40 And Sheet.Name <> RefSheet.Cells(RefRow, 6).Value Then   'This ensures only Sheet11 through Sheet40 are affected, and only if their names are not the default.

            UpdateTMSheet Sheet, RefSheet.Cells(RefRow, 5).Value, RefSheet.Cells(RefRow, 6).Value, RefSheet.Cells(RefRow, 9).  'Passes a range, two strings, and another range to UpdateTMSheet.
   
        End If

    Next Sheet
       
    Sheets("Setup and Update Status").Activate
   
    Application.ScreenUpdating = True

End Sub

Unfortunately, this code throws a 1004: Application-defined or Object-Defined Error at the "IF" statement.

Trying to do my own debugging, I've confirmed that the values of Counter, Sheet.Name, RefSheet, and RefRow are fine, so clearly the problem is the CELLS portion of the term RefSheet.Cells(RefRow, 6).Value.

I understand that CELLS is a property rather than a range object, but I can't seem to understand why that term doesn't work (and probably why it won't work on the next row, either.

Still on the learning curve, so apologies if this is a simple issue that I can't get.

Thanks.

 

[Cubist]

Try this on a copy.

Sub UpdateWorksheets()
    Dim RefSheet As Worksheet
    Dim targetSheet As Worksheet
    Dim i As Integer

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    Set RefSheet = ThisWorkbook.Worksheets("Lookups and Calculations")

    For i = 11 To 40
        Set targetSheet = ThisWorkbook.Sheets("Sheet" & i)
        UpdateTMSheet targetSheet, _
                      RefSheet.Range("E" & i - 7).Value, _
                      RefSheet.Range("F" & i - 7).Value, _
                      RefSheet.Range("I" & i - 7))
    Next i

    ThisWorkbook.Sheets("Setup and Update Status").Activate

    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub

 

[StephenCrump]

... clearly the problem is the CELLS portion of the term RefSheet.Cells(RefRow, 6).Value

Not exactly. The problem is with RefRow.

With Sheet1, for example, you calculate RefRow as -11. And reference to Cells(-11,6) will throw the 1004 error.

@Cubist's approach should avoid this problem by looping only through the required sheets: Sheet11 to Sheet40.

 

[USNA91]

Bloody hell...

I seem to recall having read somewhere that you could NOT do this:

 For i = 11 To 40
        Set targetSheet = ThisWorkbook.Sheets("Sheet" & i)

My attempt in the original post was to get the same functionality without doing what you did.

Well, I'm very grateful that you corrected that misconception, and will use your way, which makes perfect sense.

Thanks so much!

 

[USNA91]

Dear Stephen,

I reckon I should have looked at the code more closely. Thanks for pointing that out.

I also just learned that there is a way to step through a program and read the variables as they go. Just have to find out how to do that on a Mac keyboard (probably time for me to keep my Function keys as Function keys and not their alternate uses).

Thanks!

 

[Cubist]

I also just learned that there is a way to step through a program and read the variables as they go. Just have to find out how to do that on a Mac keyboard (probably time for me to keep my Function

The keyboard to step through for Macs is Command + Shift + I

 

[USNA91]

Thanks!

 

[USNA91]

Cubist,

Could I trouble you to clarify why you used this:

Application.Calculation = xlCalculationManual

<Other Stuff>
   
Application.Calculation = xlCalculationAutomatic

Is this just a way to speed things up, or does it have other uses?

Thanks!

 

[Cubist]

It speeds things out when you have complex formulas or large amount. By turning off automatic calculation, you can make changes to the workbook without XL constantly recalculating the formulas after each change. Then turn it back on once the changes have been made.

 

[USNA91]

Excellent. Thanks again!