vba query

[Johnsmith999]

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

 

[Fluff]

How about

Sub JohnSmith999()
   Columns(2).Insert xlToRight
   Range("B1").Value = "Postcode"
   With Range("B2", Range("A" & Rows.Count).End(xlUp).Offset(, 1))
      .Value = Evaluate(Replace("If({1},TRIM(RIGHT(SUBSTITUTE(@,"""",REPT("" "",10)),20)))", "@", .Offset(, -1).Address))
   End With
End Sub

Although I think that there is something wrong with the formula you posted.