Dynamic variable in VBA

Question

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.

Vityata · Accepted Answer · 2017-08-01 08:43:31Z

3

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

answered Aug 1, 2017 at 8:43

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Shai Rado · Accepted Answer · 2017-08-01 08:48:21Z

2

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)"

answered Aug 1, 2017 at 8:48

Shai Rado

33.7k7 gold badges32 silver badges55 bronze badges

2 Comments

Shai Rado Over a year ago

@Vityata where did he mention he needs a loop? I think he wants the fomrula dragged according to the row number in column "E"

Vityata Over a year ago

Seems legit in a way.

Michał Turczyn · Accepted Answer · 2017-08-01 08:44:21Z

1

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

answered Aug 1, 2017 at 8:44

Michał Turczyn

41.2k18 gold badges58 silver badges87 bronze badges

Collectives™ on Stack Overflow

Dynamic variable in VBA

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related