Search code examples
excelvbarubberduck

Why is referencing sheet by index still implicity referencing referencing by activesheet

I Have the below code which formats a sheet. There are multiple sheets in this workbook so i only want to take actions on the first one. However if i have any sheet other than the first activated such as the second the code throws a 1004 error. I am not sure why as i am specifying and defining the target worksheet.

Option Explicit

Sub SelectByJobNumber()
    Dim LastRow As Long
    Dim OperatingRow As Variant
    Dim CountOfMatching As Long
    
    Dim WB As Worksheet
    Set WB = ThisWorkbook.Worksheets(1)
    
    LastRow = WB.Cells(ActiveSheet.Rows.Count, "C").End(xlUp).Row
    
        WB.Range(Cells(LastRow + 2, 19), Cells(LastRow + 2, 22)).Formula = "=" & "Sum(S74:S" & LastRow & ")"
        WB.Range(Cells(LastRow + 2, 19), Cells(LastRow + 2, 22)).NumberFormat = "$#,##0.00"
    
    For OperatingRow = 74 To LastRow
        
        If WB.Cells(OperatingRow, 3) = WB.Cells(OperatingRow + 1, 3) Then

            CountOfMatching = CountOfMatching + 1

        Else
            
                WB.Range(Cells(OperatingRow - CountOfMatching, 3), Cells(OperatingRow, 21)).BorderAround ColorIndex:=1, Weight:=xlMedium
                WB.Cells(OperatingRow - CountOfMatching, 22) = Application.Sum(Range(Cells(OperatingRow - CountOfMatching, 21), Cells(OperatingRow, 21)))
                
                If WB.Cells(OperatingRow - CountOfMatching, 22) = 0 Then
                    WB.Cells(OperatingRow - CountOfMatching, 23) = "Text for Zero Total Payable"
                Else
                    WB.Cells(OperatingRow - CountOfMatching, 23) = "Not Paid"
                End If
                
                WB.Cells(OperatingRow - CountOfMatching, 22).NumberFormat = "$#,##0.00"

            CountOfMatching = 0
            
        End If
        
    Next OperatingRow
    
    If WB.Cells(LastRow + 2, 21) = WB.Cells(LastRow + 2, 22) Then
        WB.Range(Cells(LastRow + 2, 21), Cells(LastRow + 2, 22)).BorderAround ColorIndex:=4, Weight:=xlMedium
    Else
        WB.Range(Cells(LastRow + 2, 21), Cells(LastRow + 2, 22)).BorderAround ColorIndex:=3, Weight:=xlMedium
    End If

End Sub

enter image description here

enter image description here

enter image description here

Solution

  • The reason is that in code like this:

     WB.Range(Cells(LastRow + 2, 19), Cells(LastRow + 2, 22))

    The object Cells isn't qualified and so it's defaulting to ActiveWorkbook.ActiveSheet.Cells(). It feels silly though because you already qualified the Range() which takes Cells() as arguments, but those arguments are their own object and must also be qualified.

    Consider instead:

    With WB
   .Range(.Cells(LastRow + 2, 19), .Cells(LastRow + 2, 22))
End With

    Or you can just qualify them each individually with WB. if that feels cleaner.