VBA changing values in multiple columns - where it should only be changing a single column

[Marklarbear]

Hi Brains Trust....

(*Work IT restrictions wont allow me to run xl2bb to upload the spreadsheet)

I have the following excel file that I import talk times into. I have macros that run that normalises the values. The macros do what they are supposed to do - however despite naming a range for certain functions to perform, the macros seem to 'grab' all columns from column B to column K and do their magic - How can I change the code so it only looks at the data in the range K36:K75 ?

Spreadsheet:

VBA Code:

Sub Normalise_AHT_Step_1()

' Normalise_Step_1 Macro
' Adjusts 0 values - PART 1


' Selects the first range - 08:00am to 10:00am
' Replace any 0 value with a AHT value of 450


Range("K36:K43").Replace What:="0", Replacement:="450", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2

' Selects the second range - 10:00am to 05:00pm
' Replace any 0 value with a AHT value of 650


Range("K44:K71").Replace What:="0", Replacement:="650", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2

' Selects third range - 05:00pm to 6:00pm
' Replace any 0 value with a AHT value of 420


Range("K72:K75").Replace What:="0", Replacement:="420", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2


' Calls AHT_Outliers_Adjustment macro

Call AHT_Outliers_Adjustment


End Sub




Sub AHT_Outliers_Adjustment()

' Selects Range

Dim a, b, i As Long, j As Long
a = Range("K36:K75")
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 9
' (Reduces any AHT from greater than 1000 to a minimum of 900)

If Len(CStr(a(i, j))) >= 4 Then
b(i, j) = "9" & 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("K36").Resize(UBound(b, 1), UBound(b, 2)).Value = b


' Calls and runs the StartWithFour macro

Call StartWithFour


End Sub



Sub StartWithFour()

' If number is greater than 1000 - changes the first digit from * to 9

Dim Addr As String
With Range("K36:K75", Cells(Rows.Count, "A").End(xlUp))
Addr = .Address
.Value = Evaluate("IF(" & Addr & ">999,900+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
' Deletes 0 values for historical days that have no data

Application.ScreenUpdating = False

Dim cell As Range
For Each cell In Range("B36:K75")
If cell < 1 Then cell.ClearContents
Next cell

Application.ScreenUpdating = True



End Sub

 

[Akuini]

Probably the red part:

Sub StartWithFour()

' If number is greater than 1000 - changes the first digit from * to 9
 
 Dim Addr As String
 With Range("K36:K75", Cells(Rows.Count, "A").End(xlUp))