VLOOKUP - Cell Data Type (TEXT)

[Robb]

I have a cell in WS1 and I use VLOOKUP to get the corresponding data in WS5.

Range("N15").Select
ActiveCell.FormulaR1C1 = _
"=IF(VLOOKUP(R[-13]C[-5],Order_DataRange,26,false)=0,"""",VLOOKUP(R[-13]C[-5],Order_DataRange,26,false))"
Range("N15").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

With the listed code, if the data type is set to TEXT it will copy the Formula to my cell not the data from WS5, but if I set the cell type to GENERAL it works fine BUT converts some data into a date (ie. 7-8 converts to 08-Jul) Help!

[Robb]

Found the answer myself, thanks.

Range("N15").Select
Selection.NumberFormat = "General" ' Set cell temporarily to "General"
ActiveCell.FormulaR1C1 = _
"=IF(VLOOKUP(R[-13]C[-5],Order_DataRange,26,false)=0,"""",VLOOKUP(R[-13]C[-5],Order_DataRange,26,false))"
Range("N15").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Selection.NumberFormat = "@" ' Put it back to "TEXT"

[Robb]

Found the answer myself, thanks.

Range("N15").Select
Selection.NumberFormat = "General" ' Set cell temporarily to "General"
ActiveCell.FormulaR1C1 = _
"=IF(VLOOKUP(R[-13]C[-5],Order_DataRange,26,false)=0,"""",VLOOKUP(R[-13]C[-5],Order_DataRange,26,false))"
Range("N15").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Selection.NumberFormat = "@" ' Put it back to "TEXT"