Lookup Text Values

[PARans]

I am using a lookup on part numbers that can begin with a zero. In order for this to work the numbers have to be formatted as text. For some reason though I can not get my macro to work correctly. The following lookup returns "#N/A" although I have formatted the numbers to text in both sheets where the numbers appear. I find that if I stop the macro, go into the number in the cell and reenter it, the formula works.

Range(Cells(2, 1), Cells(lastrow, 2)).NumberFormat = "@" 'for text formating

With Range("R2").Resize(lastrow - 1)
.Offset.Formula = "=VLOOKUP(B2,'MIX PREVIOUS'!B:R,17,FALSE)"
.Offset.Copy
.Offset.Select
.Offset.PasteSpecial Paste:=xlPasteValues
.Offset.Application.CutCopyMode = False
End With

Help please!

 

[Andrew Poulsom]

Formatting only affects the appearance of data, not the actual data. You may find this helpful:

http://www.contextures.com/xlFunctions02.html#trouble

 

[PARans]

Thank you for your response and the useful resource.

With further examination last night, I saw that the numbers had some trailing blanks spaces, so I converted the cells to text and removed any trailing spaces with the following code. Working great now.

'Format part numbers to text
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
Range(Cells(2, 1), Cells(lastrow, 2)).NumberFormat = "@" 'for text formating

'Trim the part number for trailing spaces on the right
For i = 2 To lastrow
Cells(i, 2).Value = RTrim(Cells(i, 2))
Next i