weird pastespecial (values)

[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.

 

[Frank Kabel]

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

 

[Guldo K]

[...] 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.

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

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.

 

[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