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?
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,324
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,324
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,002
Messages
5,545,439
Members
410,684
Latest member
LakTik
Top