Sub Inc_Amount()
Dim r As Long
Dim i As Integer
Dim nxaddr As String
addresses = Array("$C$4", "$d$4", "$E$4", "$F$4", "$G$4", "$H$4", "$I$4", "$J$4", "$K$4", _
"$L$4", "$M$4", "$N$4", "$O$4", "$P$4", "$Q$4", "$R$4", "$S$4", "$T$4")
formulax = "=MAX($C$4,$D$4,$E$4,$F$4,$G$4,$H$4,$I$4,$J$4,$K$4,$L$4,$M$4,$N$4,$O$4,$P$4,$Q$4,$R$4,$S$4,$T$4)"
Application.screenupdating=false
For r = 4 To 171
Range("AA" & r).Formula = formulax
For i = 0 To 17
If Range(addresses(i)) = Range("AA" & r) Then
If Len(addresses(i)) = 4 Then n = 1 Else n = 2
nxaddr = UCase(Left(addresses(i), 3) & Right(addresses(i), n) + 1)
formulax = Replace(formulax, UCase(addresses(i)), nxaddr)
Range("AB" & r) = UCase(addresses(i))
addresses(i) = nxaddr
Exit For
End If
Next i
Next r
Application.screenupdating=True
End Sub
RUN button to execute macro
Results in column AA and AB
NOTE: there is currently no check for duplicates.
QUESTION: What is test for "completion": macro current executes the rows you have determined i.e finishes on row 171
Bookmarks