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

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,774
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
 

cforrester1981

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

cforrester1981

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

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,774
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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
 

cforrester1981

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

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,817
Messages
5,772,456
Members
425,760
Latest member
paphon

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
Top