weird pastespecial (values)

G

Guldo K

Hello.
I have a puzzling problem with a macro.
This macro copies some columns (numbers and text) from a sheet, and
pastes its values into another sheet. This works.
Then I have a sheet with a vlookup formula that points to those
numbers.
I mean, say the numbers are copied from WorkBook1 into
WorkBook2-sheet1 with a pastespecial-values statement; then in
WorkBook2-sheet2 I have a formula like this:
=vlookup(a2,sheet1!a:c,2,false)
well, this formula returns a #N/D error.
The puzzling issue is that if I select sheet1 and re-insert the values
by typing, one by one, the formula works.
Of course I'd like the macro copy-pastevalues to be enough...
Why does it happen?!?
(I hope the explanation be quite clear)

Thank you.
 
F

Frank Kabel

Hi
you probably have some invisible characters in it or a different format
(e.g. numbers stored as text, etc.)
 
G

Guldo K

Frank Kabel said:
Guldo K said:
[...] I mean, say the numbers are copied from WorkBook1 into
WorkBook2-sheet1 with a pastespecial-values statement; then in
WorkBook2-sheet2 I have a formula like this:
=vlookup(a2,sheet1!a:c,2,false)
well, this formula returns a #N/D error.
The puzzling issue is that if I select sheet1 and re-insert the
values by typing, one by one, the formula works.
Click to expand...

you probably have some invisible characters in it or a different
format (e.g. numbers stored as text, etc.)
Click to expand...

You're right, the original numbers in WorkBook1 were text, actually.
I thought adding a column like '=x1' (where in x1 there's the fake
number) could be enough, but it's not. A '=value(x1)' is needed.
So now it's fine.

Thank you vey much.
 
B

BrianB

A common problem. Make sure columns are formatted as text.
Instead of typing each one .. select the column and use Data/Text t
Columns .. Finish
 
Top