How to make change on alphabet incrementally in VBA editor across 50 Excel files?

Question

I have 50 identical Microsoft Excel files which the formula in each file are the same. But I want to adjust the formula in each file. The formula containing the numbers can be adjusted properly and increased incrementally as I want. But the problem come when I want to increase the alphabet order incrementally such that I want "SHEET'!D" change to "SHEET'!E" for file no. 2, "SHEET'!F" for file no. 3 and so on until "SHEET'!BA" for file no. 50. Below are the codes I had been working on.

Sub UpdateFormulasAcrossSheets()
    Dim i As Integer
    
    For i = 2 To 50
        Dim wb As Workbook
        Dim ws As Worksheet
        
        ' Open the file
        Set wb = Workbooks.Open("C:\Users\User\Desktop\GAJI\PEKERJA " & i & " SLIP.xlsx")
        
        For Each ws In wb.Sheets
            ' Replace formulas across all sheets
            ws.Cells.Replace What:="$11", Replacement:="$" & (i + 10), LookAt:=xlPart, _
                SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                ReplaceFormat:=False
        
            ws.Cells.Replace What:="INDEX'!C12", Replacement:="INDEX'!C" & (i + 11), LookAt:=xlPart, _
                SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                ReplaceFormat:=False
        
            ws.Cells.Replace What:="INDEX'!D12", Replacement:="INDEX'!D" & (i + 11), LookAt:=xlPart, _
                SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                ReplaceFormat:=False
        
            ws.Cells.Replace What:="SHEET'!D", Replacement:="SHEET'!" & Chr(i + 3), LookAt:=xlPart, _
                SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                ReplaceFormat:=False
        Next ws
        
        ' Save and close the file
        wb.Close SaveChanges:=True
    Next i
End Sub

This may help ... https://stackoverflow.com/questions/12796973/function-to-convert-column-number-to-letter — user10186832, Aug 21 '23 at 04:31

score 0 · Accepted Answer · answered Aug 21 '23 at 03:36

Getting column names with Chr() has limitations beyond Column Z. Certainly a recursive function can accomplish this. However, a more concise approach is using the cell address to quickly get the column name.

    ws.Cells.Replace What:="SHEET'!D", Replacement:="SHEET'!" & _
        Split(Cells(1, 3 + i).Address, "$")(1), LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

Testing code:

Sub test()
    For i = 2 To 50
        Debug.Print i, "'SHEET1'!" & Split(Cells(1, 3 + i).Address, "$")(1)
    Next
End Sub

Output

 2            'SHEET1'!E
 3            'SHEET1'!F
 ...
 49           'SHEET1'!AZ
 50           'SHEET1'!BA

How to make change on alphabet incrementally in VBA editor across 50 Excel files?

1 Answers1