Johnsmith999
New Member
- Joined
- Dec 27, 2018
- Messages
- 3
I use the following formula to extract postcodes from addresses and it works perfectly.
TRIM(RIGHT(SUBSTITUTE(A2,"",REPT(" ",10)),20))<o
></o
>
However, when I insert the same formula in a vba loop, it does not allow me to execute. REPT is highlighted and the dialogue box says "Compile error: Sub or Function not defined"
Does anyone have a solution? the macro I have written is pasted below:
Sub tob_report()
Dim r As Integer
r = 2
Do Until Cells(r, 1) = ""
Columns(2).Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Cells(1, 2).Select
ActiveCell.Value = "Postcode"
Cells(2, 2).Select
ActiveCell.Value = Application.WorksheetFunction.Trim(Right(Substitute(Cells(2, 1), "", Rept("", 10)), 20))
r = i + 1
Loop
End Sub
TRIM(RIGHT(SUBSTITUTE(A2,"",REPT(" ",10)),20))<o


However, when I insert the same formula in a vba loop, it does not allow me to execute. REPT is highlighted and the dialogue box says "Compile error: Sub or Function not defined"
Does anyone have a solution? the macro I have written is pasted below:
Sub tob_report()
Dim r As Integer
r = 2
Do Until Cells(r, 1) = ""
Columns(2).Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Cells(1, 2).Select
ActiveCell.Value = "Postcode"
Cells(2, 2).Select
ActiveCell.Value = Application.WorksheetFunction.Trim(Right(Substitute(Cells(2, 1), "", Rept("", 10)), 20))
r = i + 1
Loop
End Sub