need formula to extract 4 digit for c1 to c3 in vb macro

[shrinivasmj]

hi'

i am trying to write a code in vb macro ,i am using below formula as

Range("C3").Select
ActiveCell.FormulaR1C1 = "=MID(R[-2]C,3,4)"
Range("C3").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("C3").Select
Application.CutCopyMode = False
Range("C3").Select

c1 8795221
c3 9522


but in my excel it shows =MID(R[-2]C,3,4)

need vb macro formula

 

[InaCell]

I tried all the different formats available, they all give the same answer.

Only way I could replicate the error was to paste the formula in manually and click Show Formulas.

Wouldn't replicate using macro. I was using 2007.