Inserting a dynamic formula using a VBA script

cforrester1981

New Member
Joined
Aug 22, 2020
Messages
11
Office Version
  1. 2016
Platform
  1. Windows
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:

VBA 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?
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
No need to loop through each cell .
VBA Code:
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
 
Upvote 0
No need to loop through each cell .
VBA Code:
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
 
Upvote 0
I've tried to
No need to loop through each cell .
VBA Code:
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)
 
Upvote 0
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.
VBA Code:
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
 
Upvote 0
Sorry, I missed the part about every 3rd row. Here's a revision.
VBA Code:
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
(y)?

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

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top