Increment Search and Replace

[akingren]

I have recorded the below macro to update a search and replace for a range of cells.

Sub time()
'
' time Macro
'
' Keyboard Shortcut: Ctrl+Shift+P
'
ActiveSheet.Range("$R$34:$Z$49").AutoFilter Field:=2
Range("T34:Z49").Select
Range("T49").Activate
Selection.Replace What:="$5", Replacement:="$6", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
ActiveSheet.Range("$R$34:$Z$49").AutoFilter Field:=2, Criteria1:="<>"
End Sub


Is there a way to make the macro increase the 'What' and 'Replacement' by 1 everytime I run it? So if I ran the macro again the Selection. Replace line would read Selection.Replace What:="$6", Replacement:="$7", LookAt:=xlPart, _

 

[Joe4]

Welcome to the Board!

Firstly, you should NEVER used reserved words (words already used by Excel or VBA for names of functions, properties, or methods) like "Time" as the name of your procedures, variables, or user defined functions. Doing so can cause errors and unexpected results. So you should re-name your procedure.

What I would do is use some unused cell on your sheet to store your value, which you can have your code refer to and increment each time.
I used cell AZ1, like this:

Sub MyTime()
'
' MyTime Macro
'
' Keyboard Shortcut: Ctrl+Shift+P
'
    Dim c As Integer
    Dim f As String
    Dim r As String
    
'   Get current value from cell AZ1
    c = Range("AZ1")
    
'   Build value to look for
    f = "$" & c
    
'   Build value to replace with
    r = "$" & (c + 1)
    
'   Make replacement
    ActiveSheet.Range("$R$34:$Z$49").AutoFilter Field:=2
    Range("T34:Z49").Select
    Range("T49").Activate
    Selection.Replace What:=f, Replacement:=r, LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
    ActiveSheet.Range("$R$34:$Z$49").AutoFilter Field:=2, Criteria1:="<>"
    
'   Increment value in cell AZ1
    Range("AZ1").Value = Range("AZ1").Value + 1
    
End Sub

You will just need to initially populate cell AZ1 with the number currently being used.
Then, it should work automatically going forward.

 

[akingren]

Amazing!!! Thank you so much Joe4. Your code works perfectly, and duly noted on the naming issue.

 

[Joe4]

You are welcome.
Glad I was able to help!