Inserting a dynamic formula using a VBA script

[cforrester1981]

Hi folks,

I am a VBA newbie and generally teaching myself the syntax by recording macros and reading the code.

However, the one problem I am encountering is how to apply a dynamic formula to a cell.

Some context: I need to extract medical practice numbers from a text string in the cell to the left. That same formula has to be applied to every 3rd row starting at row 3 the end of the document (it is from a monthly report and the rows differ).

Here is the data example:

Cell A3: Dr John Smith ( Pr: 1234567)

I just want cell B3 to contain 1234567 - The Excel formula I'm using =(MID(B3,FIND("( ",B3)+6,7))

Here is my code:

Sub ExtractPrNumber()

    Dim r As Long
    
'   Set initial row to insert first formula on
    r = 3
    
'   Loop until column A is blank
    Do Until Cells(r, "A") = ""
'       Insert formulas
        Cells(r, "B").FormulaR1C1 = "=MID(RC[-1],FIND("( ",RC[-1])+6,7))"
'       Add 3 to row counter
        r = r + 3
    Loop
    
End Sub

Now I get a syntax error on the line where the formula is applied. What am I doing wrong?

 

[JoeMo]

No need to loop through each cell .

Sub ExtractPrNumber()
Dim r As Long, LR As Long
'   Set initial row to insert first formula on
    r = 3
    LR = Range("A" & Rows.Count).End(xlUp).Row
    Range("B" & r, "B" & LR).Formula = "=(MID(A3,FIND(""( "",A3)+6,7))"
End Sub

 

[cforrester1981]

No need to loop through each cell .

Sub ExtractPrNumber()
Dim r As Long, LR As Long
'   Set initial row to insert first formula on
    r = 3
    LR = Range("A" & Rows.Count).End(xlUp).Row
    Range("B" & r, "B" & LR).Formula = "=(MID(A3,FIND(""( "",A3)+6,7))"
End Sub

This has worked but it applies it to every line instead of every third line. That's why I need to put in the loop

 

[cforrester1981]

I've tried to

No need to loop through each cell .

Sub ExtractPrNumber()
Dim r As Long, LR As Long
'   Set initial row to insert first formula on
    r = 3
    LR = Range("A" & Rows.Count).End(xlUp).Row
    Range("B" & r, "B" & LR).Formula = "=(MID(A3,FIND(""( "",A3)+6,7))"
End Sub

OK, when I try apply the formula in your code to my code, it does not apply it dynamically (the same formula is applied to every third cell)

 

[JoeMo]

I've tried to

OK, when I try apply the formula in your code to my code, it does not apply it dynamically (the same formula is applied to every third cell)

Sorry, I missed the part about every 3rd row. Here's a revision.

Sub ExtractPrNumber()
Dim r As Long, LR As Long
'   Set initial row to insert first formula on
    r = 3
    LR = Range("A" & Rows.Count).End(xlUp).Row
    Do
        Range("B" & r).Formula = "=(MID(A" & r & ",FIND(""("",A" & r & ")+6,7))"
        r = r + 3
    Loop While r <= LR
End Sub

 

[cforrester1981]

Sorry, I missed the part about every 3rd row. Here's a revision.

Sub ExtractPrNumber()
Dim r As Long, LR As Long
'   Set initial row to insert first formula on
    r = 3
    LR = Range("A" & Rows.Count).End(xlUp).Row
    Do
        Range("B" & r).Formula = "=(MID(A" & r & ",FIND(""("",A" & r & ")+6,7))"
        r = r + 3
    Loop While r <= LR
End Sub

?

Awesome. Thank you @JoeMo, It worked perfectly. I will use what I have learnt here to build the 3 other macros I need.

 

[JoeMo]

?

Awesome. Thank you @JoeMo, It worked perfectly. I will use what I have learnt here to build the 3 other macros I need.

You are welcome - thanks for the reply.