Help with VBA code when selecting next sheet in workbook

[Marklarbear]

Hi Guys

I have some VBA code that I got assistnace with from the MrExcel fraternity previously.... what I want it to do now is to run the same steps on the next sheet in the book (I have changed the text to orange colour from where the next sheet kicks in).

I'm getting the following error when moving to the next sheet:

it identifies 'Dim a' as the issue - is the fix as easy as changing the Dim assignments? ie changing all the DIM references to different letters when the next sheet is selected? or is there another way?





VBA Code:

Sub AHT_Outliers_Adjustment()

' Friday AHT Sheet.
' Changes the first digit if greater than 999 or less than 100 to target AHT range

Dim a, b, i As Long, j As Long
a = Range("B4:K99")
ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2))

For i = 1 To UBound(a, 1)
For j = 1 To UBound(a, 2)

' If number is greater than 4 digits - remove thousands digits and change hundred digit to 4
' (Reduces any AHT from greater than 1000 to a minimum of 400)

If Len(CStr(a(i, j))) >= 4 Then
b(i, j) = "4" & Right(a(i, j), 2)

' If number is greater than 1 digit but less than 3 digits - add a 2 to the front (increases a 2 digit AHT to a 3 digit AHT)

ElseIf Len(CStr(a(i, j))) = 2 Then
b(i, j) = "2" & Right(a(i, j), 2)

' If number is less than 2 digits - add 20 to the front (increases a single digit AHT to a 3 digit AHT)

ElseIf Len(CStr(a(i, j))) = 1 Then
b(i, j) = "20" & Right(a(i, j), 2)
Else
b(i, j) = a(i, j)
End If
Next j
Next i

Range("B4").Resize(UBound(b, 1), UBound(b, 2)).Value = b

' Calls and runs the StartWithFour macro

Call StartWithFour

' calls and runs the ClearCells macro

Call ClearCells

' Repeat outlier steps for Saturday AHT sheet.

Sheets("Saturday AHT").Select

' Changes the first digit if greater than 999 or less than 100 to target AHT range

Dim a, b, i As Long, j As Long
a = Range("B4:K99")
ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2))

For i = 1 To UBound(a, 1)
For j = 1 To UBound(a, 2)

' If number is greater than 4 digits - remove thousands digits and change hundred digit to 4
' (Reduces any AHT from greater than 1000 to a minimum of 400)

If Len(CStr(a(i, j))) >= 4 Then
b(i, j) = "4" & Right(a(i, j), 2)

' If number is greater than 1 digit but less than 3 digits - add a 2 to the front (increases a 2 digit AHT to a 3 digit AHT)

ElseIf Len(CStr(a(i, j))) = 2 Then
b(i, j) = "2" & Right(a(i, j), 2)

' If number is less than 2 digits - add 20 to the front (increases a single digit AHT to a 3 digit AHT)

ElseIf Len(CStr(a(i, j))) = 1 Then
b(i, j) = "20" & Right(a(i, j), 2)
Else
b(i, j) = a(i, j)
End If
Next j
Next i

Range("B4").Resize(UBound(b, 1), UBound(b, 2)).Value = b

' Calls and runs the StartWithFour macro

Call StartWithFour

' calls and runs the ClearCells macro

Call ClearCells

End Sub

 

[StephenCrump]

The message means exactly what it says. You only need to declare your variables once within any one Sub, You've done it twice, hence the error message.

Rather than repeating your code lines, you can loop through the two worksheets like this ...

Sub AHT_Outliers_Adjustment()

    Dim ws As Worksheet
    Dim a As Variant, b As Variant
    Dim i As Long, j As Long
    
    For Each ws In Worksheets(Array("Friday AHT", "Saturday AHT"))
        a = ws.Range("B4:K99")
        ReDim b(1 To UBound(a), 1 To UBound(a, 2))
        '...etc
    Next ws
        
End Sub

 

[Marklarbear]

The message means exactly what it says. You only need to declare your variables once within any one Sub, You've done it twice, hence the error message.

Rather than repeating your code lines, you can loop through the two worksheets like this ...

Sub AHT_Outliers_Adjustment()

    Dim ws As Worksheet
    Dim a As Variant, b As Variant
    Dim i As Long, j As Long
   
    For Each ws In Worksheets(Array("Friday AHT", "Saturday AHT"))
        a = ws.Range("B4:K99")
        ReDim b(1 To UBound(a), 1 To UBound(a, 2))
        '...etc
    Next ws
       
End Sub

i made the change but it doesnt cycle to the next worksheet..... ? for clarity - below is the full code that i'm looking to use (as my original post was a sample of the full code):

Sub Normalise_AHT_Step_1()
'
' Normalise_Step_1 Macro
' Adjusts 0 values - PART 1
'

'
    Sheets(Array("Friday AHT", "Saturday AHT", "Sunday AHT", "Monday AHT", "Tuesday AHT", "Wednesday AHT", "Thursday AHT")).Select
    Sheets("Friday AHT").Activate
    
' Selects the first range - 00:00am to 07:45am
    
    Range("B4:K35").Select
    
' Replace any 0 value with a AHT value of 320
    
    Selection.Replace What:="0", Replacement:="320", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
    
' Selects the second range - 08:00am to 05:45pm
    
    Range("B36:K75").Select
    
' Replace any 0 value with a AHT value of 390
    
    Selection.Replace What:="0", Replacement:="390", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
    
' Selects third range - 06:00pm to 11:45pm
    
    Range("B76:K99").Select
    
' Replace any 0 value with a AHT value of 440
    
    Selection.Replace What:="0", Replacement:="440", LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
    Range("B35").Select
    Sheets("Friday AHT").Select

' Calls AHT_Outliers_Adjustment macro

Call AHT_Outliers_Adjustment


End Sub

Sub AHT_Outliers_Adjustment()
    
 ' Changes the first digit if greater than 999 or less than 100 to target AHT range
    
 Dim ws As Worksheet
    Dim a As Variant, b As Variant
    Dim i As Long, j As Long
    
    For Each ws In Worksheets(Array("Friday AHT", "Saturday AHT", "Sunday AHT", "Monday AHT", "Tuesday AHT", "Wednesday AHT", "Thursday AHT"))
        a = ws.Range("B4:K99")
        ReDim b(1 To UBound(a), 1 To UBound(a, 2))
    
    For i = 1 To UBound(a, 1)
        For j = 1 To UBound(a, 2)
        
 ' If number is greater than 4 digits - remove thousands digits and change hundred digit to 4
 ' (Reduces any AHT from greater than 1000 to a minimum of 400)
 
            If Len(CStr(a(i, j))) >= 4 Then
                b(i, j) = "4" & Right(a(i, j), 2)
                
 ' If number is greater than 1 digit but less than 3 digits - add a 2 to the front (increases a 2 digit AHT to a 3 digit AHT)
            
            ElseIf Len(CStr(a(i, j))) = 2 Then
                b(i, j) = "2" & Right(a(i, j), 2)
                
 ' If number is less than 2 digits - add 20 to the front (increases a single digit AHT to a 3 digit AHT)
 
            ElseIf Len(CStr(a(i, j))) = 1 Then
                b(i, j) = "20" & Right(a(i, j), 2)
            Else
                b(i, j) = a(i, j)
            End If
        Next j
    Next i
       
    Range("B4").Resize(UBound(b, 1), UBound(b, 2)).Value = b

' Calls and runs the StartWithFour macro

Call StartWithFour

' calls and runs the ClearCells macro

Call ClearCells
    
Next ws
    
End Sub


Sub StartWithFour()

' If number is greater than 800 - changes the first digit from * to 4
  
  Dim Addr As String
  With Range("B4:K99", Cells(Rows.Count, "A").End(xlUp))
    Addr = .Address
    .Value = Evaluate("IF(" & Addr & ">800,400+MOD(" & Addr & ",100)," & Addr & ")")
    
End With

' calls and runs the ClearCells macro

Call ClearCells

End Sub

Sub ClearCells()

 ' Remove any 0 values from range - PART 2
 
    Application.ScreenUpdating = False
    
    Dim cell As Range
    For Each cell In Range("B4:K99")
        If cell < 1 Then cell.ClearContents
    Next cell
    
    Application.ScreenUpdating = True
    
End Sub

 

[StephenCrump]

An unqualified reference to Range("B4:K35"), say, will refer to B4:K35 on the ActiveSheet, whereas you want to loop through this range on several sheets.

Here's how you could do this, without all the unnecessary .Select, .Selection etc

Sub Normalise_AHT_Step_1()
    
    Dim ws As Worksheet
    
    For Each ws In Sheets(Array("Friday AHT", "Saturday AHT", "Sunday AHT", "Monday AHT", "Tuesday AHT", "Wednesday AHT", "Thursday AHT"))
        With ws
            .Range("B4:K35").Replace What:="0", Replacement:="320", LookAt:=xlWhole, _
                SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
                
            .Range("B36:K75").Replace What:="0", Replacement:="390", LookAt:=xlWhole, _
                SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
            '... etc
        
        End With
    Next ws
End Sub