Clever Geek Handbook

Dynamic variable in VBA

I have this VBA Script and I don't know how to make a dynamic bold section so that the formula will be =StripAccent(C2)

, =StripAccent(C3)

etc. for everyone i

.

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

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

+3


source to share


3 answers


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 is the use Cells(i,3)

.



Edit: If you use a custom function here - Convert Special Characters to Alphabet , then something like this might work (but not like 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
+3


source


In your case, you don't need a loop, you can directly add Formula

to your entire range, for example:

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


Or even a "cleaner":

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


source 


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


source






More articles:


All Articles