Storing Numbers as Text VBA

[doubles011]

I am trying to write a macro that I will use to insert vlookups into a number of different workbooks. The problem I am having is that the lookup array is composed of numbers stored as text in the source material, while the lookup values are stored as "general" in the workbooks in which I am inserting the vlookups. I tried just changing the lookup array to general, but it eliminates leading 0s that i need to keep. I also tried changing the lookup values to text, but this required me to go through and double click each cell so that the number would actually be stored as text. I then tried writing some code that would insert a column, change the number format to text in the blank column and then copy the lookup values and paste values in the new text column -- but that did not work either (using code below). Does anyone have any suggestions about how i can get these lookup values to be text without going through and individually selecting them all? Thanks

Columns("E:E").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

Selection.NumberFormat = "@"

Columns("D:D").Copy

Columns("E:E").PasteSpecial Paste:=xlPasteValues

 

[Snakehips]

doubles011,

Welcome to Mr Excel.

Does it help if you modify your VLOOKUP formulas along these lines?
Assumes your numbers are 6 digits with leading zeros.

Excel 2007
	A	B	C	D	E	F
1	234	d			000234	d
2	123456	e			001234	a
3	9998	f			009998	f
4					026353	b
5					123456	e
6					237251	c
Sheet1

Cell Formulas
Range	Formula
B1	=VLOOKUP(TEXT(A1,"000000"),$E$1:$F$6,2)

Hope that helps.