Storing numbers as text for VLOOKUP

I am aggregating a large amount of data from several workbooks and need to cross-reference several thousand items, perfect job for VLOOKUP.

However, all I get is the lovely #NA error.

The issue of course is type mismatch like always. For some reason, selecting the cells and setting the type to text is not enough, you have to enter into the cell and then hit enter (F2 + Enter, or use the code provided below). Is there a better way to force data to be of a certain type using built in excel functions? I've tried everything from format painter, text to columns, etc. and get no results.

VBA code I used as a work-around:

Sub Macro3()
' Select the starting cell, macro will work it's way down the list changing nothing.
'
Dim variable As String
Dim t As Integer

Application.ScreenUpdating = False
Application.DisplayAlerts = False

For t = 0 To 500
    variable = ActiveCell.FormulaR1C1
    ActiveCell.FormulaR1C1 = variable
    ActiveCell.Offset(1, 0).Range("A1").Select
Next t

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

Recent Questions...