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]

Hi

Do you sample data and sample expected result?

Do you have a formula that works in excel without the R1C1 conversion?

Cheers, InaCell.

 

[Michael M]

Sub MM1()
With Range("C3")
   .Formula = "=MID(C1,3,4)"
   .Value = .Value
End With
End Sub

 

[shrinivasmj]

need to extract data from cell c1 with is ryy4552

in c3 cell the data should com y455

leveing first 2 digit take next four digit. in cell c3

 

[shrinivasmj]

but the out out in exel shows

=MID(C1,3,4)

 

[Michael M]

Sub MM1()
With Range("C3")
   .Formula = "=MID(C1,4,4)"
   .Value = .Value
End With
End Sub

puts 4552 in cell c3 from ryy4552 in cell C1

 

[InaCell]

Hi,

What Michael posted works for me using Excel 2007.

 

[shrinivasmj]

hi

i have lot of files were i use macro to run it

Sub MM1()
With Range("C3")
.Formula = "=MID(C1,4,4)"
.Value = .Value
End With
End Sub
in all files its shows out put as in cell c3 =MID(C1,3,4)

 

[InaCell]

Hi

You have a real problem if

[COLOR=#333333]"=MID(C1,4,4)"[/COLOR]

shows as =MID(C1,3,4) in C3.

 

[Michael M]

It probably means the cell is formatted as text !!