vlookup returning #n/a

[Diane]

I am having a problem with getting vlookup to return the
correct value. The spreadsheet that contains the lookup
value is imported from Access. The lookup table is in a
second workbook.

When I enter the formula initially, I get #N/A. All I have
to do to get the correct value from the lookup table is to
select the lookup value on the original sheet, press F2,
then enter. Suddenly, the #N/A changes to the correct
value. However, this would be a rather time consuming fix
since I would have to manually edit 1000 rows.

Any suggestions?

 

[Don Guillett]

try this
Sub FixRangeValues()
For Each C In Selection
C.Value = Format(C, "00")
Next
End Sub

 

[Diane]

Nevermind. I found my answer on another site. Thanks.

 

[Gord Dibben]

Diane

I would suggest that the values you get from Access are brought in as Text
rather than Numbers. That's why the F2>Enter works. It changes the value
back to a real Number.

Copy an empty cell. Select your range of "values" and Paste
Special>Add>OK>Esc.

Gord Dibben Excel MVP - XL97 SR2 & XL2002

 

[Gord Dibben]

So what was the answer? Nice to share so the rest of us can learn also.

Thanks, Gord

 

[Corina]

Hi Diane,

Just wondering if you could share your resolution to this
problem with me, or the site you found it on, since I am
having the exact same issue.

Thanks!
Corina

 

[tc]

Hi,

I guess that the problem arises because excel just recognised the
imported data as text format even though the format is already set as
"General" or "Number".

I just solve this problem by adding a new column and adding 1 and
subtracting 1 (i.e. +1-1) to the original value. The effect is to force
excel to calculate the cell value and make it a "calculation enabled"
cell. Then u can make any others calculation.

Actually, I don't think it's the best solution but it's the only
solution that I can think of. It's appreciated that if anyone could give
a better answer.

Thanks!

TC.

 

[Gord Dibben]

TC

No need for an helper column.

Copy an empty cell. Select your range you want to convert. Paste
Special>Add>OK>Esc.

Gord Dibben Excel MVP - XL97 SR2 & XL2002

 

[Myrna Larson]

If you use a helper column (which you don't need -- see Gord's reply), there's no need to add 1,
then subtract 1 -- you can simply add 0.