cforrester1981
New Member
 Joined
 Aug 22, 2020
 Messages
 11
 Office Version

 2016
 Platform

 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:
Now I get a syntax error on the line where the formula is applied. What am I doing wrong?
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?