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.

 

[USNA91]

uh oh... Problem!

Option Explicit

Sub UpdateWorksheets()

Dim RefSheet As Worksheet           'Tthe worksheet containing lookup values.
Dim TargetSheet As Worksheet        'The worksheet being evaluated or updated.
Dim Count As Integer                'A variable used to count within the For loop.
Dim DfltSheetName As String         'The default name corresponding to Sheet XX.
Dim CalcSheetName As String         'The calculated name for Sheet XX.
Dim LastUpdate As Range             'The Range that stores the date & time that Sheet XX was last updated.


Application.ScreenUpdating = False                  'Turned off to prevent screen flicker.
Application.Calculation = xlCalculationManual       'Sets Excel to manual calculation to speed up code execution.
   
   
    Set RefSheet = ThisWorkbook.Worksheets("Lookups and Calculations")
 
    For Count = 11 To 40         ' Counts from Worksheet Codenames "Sheet11" to "Sheet40".
   
        Set TargetSheet = ThisWorkbook.Sheets("Sheet" & Count)
        Set LastUpdate = RefSheet.Range("I" & Count - 7)
        DfltSheetName = RefSheet.Range("E" & Count - 7).Value
        CalcSheetName = RefSheet.Range("F" & Count - 7).Value
       
        With TargetSheet
    
            If .Name = DfltSheetName And .Visible = True Then
             
                    'If the name of the Worksheet is the Default Name AND the Worksheet is visible, then a Team
                    'Member has been deleted from Worksheet "Setups and Update Status" since the last time this
                    'routine was run. This IF statement detects that scenario and resets the sheet for later use
                    'by another Team Member by erasing any data entered by the prior Team Member, homing the
                    'window view/scroll to the upper-left corner of the Worksheet, homing the cursor to Cell C7,
                    'hiding the Worksheet, and reseting the "Last Update" value for the Worksheet.
                   
                    .Activate
                    .Range("C7:I8,C10:I11,C13:I14,C16:I17,C19:I20,C22:I23,C25:I26,C28:I29").ClearContents
                    .Range("C31:I32,C34:I35,C37:I38,C40:I41,C43:I44,C46:I47,C49:I50,C52:I53").ClearContents
                    .Range("C55:I56,C58:I59,C61:I62,C64:I65,C67:I68,C70:I71,C73:I74,C76:I77").ClearContents
                    .Range("C79:I80,C82:I83,C85:I86,C88:I89,C91:I92,C94:I95,C97:I98,C100:I101").ClearContents
                    .Range("C103:I104,C106:I107,C109:I110,C112:I113,C115:I116,C118:I119,C121:I122").ClearContents
                    .Range("C124:I125,C127:I128,C130:I131,C133:I134,C136:I137,C139:I140,C142:I143").ClearContents
                    .Range("C145:I146,C148:I149,C151:I152,C154:I155,C157:I158,C160:I161,J6:L161").ClearContents
                    .Range("A1").Select
                    .Range("C7").Select
                    .Visible = False
                    LastUpdate.Value = "Pending Data Entry"
          
            End If
           
            If .Name <> DfltSheetName Then
       
                    'If the name of the Worksheet is not Default Name, then it is intended for use by a Team Member identified in
                    'Worksheet "Setups and Update Status". This Else statement ensures the sheet is visible for use.
               
                    .Visible = True
   
            End If
           
        End With
       
    Next Count
          
ThisWorkbook.Sheets("Setup and Update Status").Activate
          
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

I am getting a Runtime Error 9: Subscript out of range error at the Set TargetSheet = ThisWorkbook.Sheets("Sheet" & Count) line. If it's throwing it there, I suspect it will throw it again on each of the next three lines.

I think I copied it correctly from Cubist's suggestion, or did I miss something?

 

[StephenCrump]

I am getting a Runtime Error 9: Subscript out of range error at the Set TargetSheet = ThisWorkbook.Sheets("Sheet" & Count) line. If it's throwing it there, I suspect it will throw it again on each of the next three lines.

For Count = 11 To 40         ' Counts from Worksheet Codenames "Sheet11" to "Sheet40"
    Set TargetSheet = ThisWorkbook.Sheets("Sheet" & Count)

The error message indicates that a sheet with this name (not codename) doesn't exist. But your comment suggests you want to loop through codenames Sheet11 to Sheet40?

If you're able and happy to set your macro security (and for any other users) to trust access to the VBA project, you can loop like this:

For Count = 11 To 40
    Set TargetSheet = ThisWorkbook.Worksheets(CStr(ThisWorkbook.VBProject.VBComponents("Sheet" & Count).Properties("Name")))

If not, you'll need to use an indirect method, e.g. looping through the Worksheets collection as you were in Post #1, and testing that .Codename is in the required range Sheet11 to Sheet40.

 

[USNA91]

Oy...

Ok, Stephen. Thanks. I'll go back to my original method and try to avoid the other error I had missed.

Cripes, it's like learning algebra all over again...

 

[Alex Blakenburg]

Do you want to show us what you have in Column F of RefSheet and explain what you are trying to achieve with this exclusion ?

Sheet.Name <> RefSheet.Cells(RefRow, 6).Value