Changing a cell from text to number in a macro

[SWF13]

Hello, can anyone help me with this. I have some code in which i need to change a number to a date but the cell returns a text value which doesnt find the value in my lookup table. I found some code which did this but for some reason its stopped working and i'm not sure if me adding something to the macro may have done this. The code is:

Columns("B:C").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("B1").Select
Range("B1", Range("A65536").End(xlUp).Offset(, 1)).Formula = "=Left(A1,5)"
Range("C2").Select
Range("C2", Range("B65536").End(xlUp).Offset(, 1)).Formula = "=If(B2>0,vlookup(B2,'Date Table'!A:B,2,))"
Range("C1").Select
ActiveCell.FormulaR1C1 = "DATE"
Columns("C:C").Select
Selection.NumberFormat = "mmm/yy"
Range("B1").Select
ActiveCell.FormulaR1C1 = "VALUE"
Range("B:B").Select
With Selection
Selection.NumberFormat = "General"
.Value = .Value
End With
Columns("I:J").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("I1").Select
Range("I1", Range("H65536").End(xlUp).Offset(, 1)).Formula = "=Left(H1,5)"
Range("J2").Select
Range("J2", Range("I65536").End(xlUp).Offset(, 1)).Formula = "=If(I2>0,vlookup(I2,'Date Table'!A:B,2,))"
Range("J1").Select
ActiveCell.FormulaR1C1 = "DATE 2"
Columns("J:J").Select
Selection.NumberFormat = "mmm/yy"
Range("I1").Select
ActiveCell.FormulaR1C1 = "VALUE 2"
Range("I:I").Select
With Selection
Selection.NumberFormat = "General"
.Value = .Value

Any help would be appreciated, i'm still new to all this but am loving learning!

 

[Joe4]

You can record yourself doing the steps manually to get the code you need.

1. Turn on the Macro Recorder
2. Highlight the column, change the Format to General or Number
3. With the column still highlighted, select Text to Columns from the Data menu.
4. Click Finish
5. Stop the Macro Recorder.

This should have converted all those entries from Text to Numeric. And if you view your VB Editor, you will see that you recorded the code you need to do this.