CODEWITHSUNDEEP
vbaexcel
Ale
Ale

Reputation: 665

How to dynamically change range inside formula?

I’m applying a formula: enter image description here

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?

Upvotes: 3

Views: 420

Answers (1)

LMM9790
LMM9790

Reputation: 486

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

Upvotes: 1

Related Questions