How to dynamically change range inside formula?

Question

I’m applying a formula:

Textual representation of formula:

=(SUBSTITUTE((LEFT(A2;(FIND("htt";A2;1))-3));";";";"))&RIGHT(A2;(LEN(A2)-(FIND("htt";A2;1))+3))

to all cells in a range A2:A10, writing a result to range B2:B10 respectively.

To do this I use the following macro:

Sub ColumnsFormat()
    Dim s As String
    Dim i As Integer, j As Integer
    'Set wb = Workbooks("CSV_File.xlsm")
    Application.ScreenUpdating = False
    j = 1

    For i = 2 To 10
        s = "=(SUBSTITUTE((LEFT(R[" & Trim(Str(i)) & "]C[" & Trim(Str(j - 2)) & "],(FIND(""htt"",R[" & Trim(Str(i)) & "]C[" & Trim(Str(j - 2)) & "],1))-3)),"","","";""))&RIGHT(R[" & Trim(Str(i)) & "]C[" & Trim(Str(j - 2)) & "],(LEN(R[" & Trim(Str(i)) & "]C[" & Trim(Str(j - 2)) & "])-(FIND(""htt"",R[" & Trim(Str(i)) & "]C[" & Trim(Str(j - 2)) & "],1))+3))"
        Sheets("Sheet1").Cells(i, 2).Value = s
    Next i
End Sub

The problem is that for some reason a row number inside the formula inside a For cycle is wrong. Instead of taking A2; A3; A4 … A10 cells, (changing row number by 1 each time), macro runs through A2; A4; A6 etc. (increasing a row number by 2 each time).

What am I doing wrong?

LMM9790 LMM9790 · Accepted Answer · 2016-03-25T11:40:49

By changing the row in the formula to 0 the code works just fine. I guess the problem is that in your formula the row was calculated relative to the specific cell the formula was afterwards applied to. Therefore the formula in B2 looked at A(2+2), in B3 at A(3+3) and so on.

    Sub ColumnsFormat()

Dim s As String
Dim i As Integer, j As Integer
'Set wb = Workbooks("CSV_File.xlsm")
Application.ScreenUpdating = False
j = 1

For i = 2 To 10
     s = "=(SUBSTITUTE((LEFT(R[" & 0 & "]C[" & j - 2 & "],(FIND(""htt"",R[" & 0 & "]C[" & Trim(Str(j - 2)) & "],1))-3)),"","","";""))&RIGHT(R[" & 0 & "]C[" & Trim(Str(j - 2)) & "],(LEN(R[" & 0 & "]C[" & Trim(Str(j - 2)) & "])-(FIND(""htt"",R[" & 0 & "]C[" & Trim(Str(j - 2)) & "],1))+3))"
     Sheets("Sheet1").Cells(i, 2).Value = s
Next i

End Sub

How to dynamically change range inside formula?

1 Answers