Help with Do While loop formula

[gllorente]

Hello,

I am trying to incorporate this formula into a VBA Do while loop:

=IF(SEARCH("~*",A1),MID(A1,6,4),0)

Basically, I want to search the cells on column 1 if it contains an asterisk. If it does, I want to extract 4 numbers out of it.

It works fine when I type the formula on a cell, but I can't seem to get it to work in VBA.

Here is how I have it in VBA:

Sub get_balance()

Dim r As Integer

r = 1
Do While Cells(r, 1) <> ""
Cells(r, 3).Value = "=IF(search(""~*"",Cells(r,1),mid(Cells(r,1),6,4),0)"
r = r + 1
Loop
End Sub


Would someone please let me know what is wrong with this code? Is it even correct to use a Do While loop, or is it better to use something else? I am new at this, and trying to learn.

I would appreciate any input! Thanks very much!!!

Gretchen

 

[Fluff]

Try

Cells(r, 3).FormulaR1C1 = "=IF(SEARCH(""~*"",rc[-2]),MID(rc[-2],6,4),0)"

 

[gllorente]

It worked! You're awesome! Thanks so much, Fluff!!

 

[Fluff]

You're welcome & thanks for the feedback