CODEWITHSUNDEEP
vbaexceldynamicformulaquotes
Matias Eiletz
Matias Eiletz

Reputation: 429

Dynamic variable in VBA

I have this VBA Script and I don't know how to make dynamic the bold section, so that the formula would be =StripAccent(C2), =StripAccent(C3) and so on for each i.

For i = 2 To 10  
    Cells(i, 5) = "=StripAccent(Ci)"
Next i

I read about double quotes, but it didn't work there.

Upvotes: 3

Views: 869

Answers (3)

Shai Rado
Shai Rado

Reputation: 33692

In your case, you don't need a loop, you can directly add the Formula to your entire range, like this:

Range(Cells(2, 5), Cells(10, 5)).Formula = "=StripAccent(C2)"

Or, even "cleaner" :

Range("E2:E10").Formula = "=StripAccent(C2)"

Upvotes: 2

Michał Turczyn
Michał Turczyn

Reputation: 37500

For i = 2 To 10  
    Cells(i, 5).Formula = "=StripAccent(C" & i & ")"
Next i

Upvotes: 1

Vityata
Vityata

Reputation: 43595

This is a possible solution:

Public Sub TestMe()
    Dim i    As Long
    For i = 2 To 10
        Cells(i, 5) = "=StripAccent(C" & i & ")"
    Next i
End Sub

Another one is to use Cells(i,3).

Edit: If you are using a custom function from here - Converting Special Characters into alphabet then something like this can work as well (but not as a formula):

Public Sub TestMe()
    Dim i    As Long
    For i = 2 To 10
        Cells(i, 5) = StripAccent(Cells(i,3))
    Next i
End Sub

Upvotes: 3

Related Questions