macro giving #N/A error

[johncena]

dear friends here i have attached my document.i have inset 3 macros to my document.in that one macro answer is 50% only working.macro name is "FINAL".if u click "FINAL" macro button that macro first sheet column "A,B,C" match with second sheet column "B,D,C" & values giving in first sheet column "F,G,H,I".in that some values giving #N/A error.pls help me i can't understand what's wrong with this....pls help.....
document password - 0

[Marcol]

Hi johncena

Ignore this, my mistake, and I can't delete the post

Sorry

[Andy Pope]

Your data in Processing!D4 has a trailing space.

Therefore the lookup fails.

[johncena]

it's ok Marcol u have help me a lot in my previous posts.

[johncena]

Your data in Processing!D4 has a trailing space.

Therefore the lookup fails.

not clear for me sir,what i can do for this,,,,,,

[Andy Pope]

remove the trailing space from the cell.

In a spare column you could use the formula =TRIM(D4)
then copy+paste special values back over D4.

[johncena]

remove the trailing space from the cell.

In a spare column you could use the formula =TRIM(D4)
then copy+paste special values back over D4.

wow thanks a lot sir it's work.REP+

[Marcol]

Well spotted Andy

Johncena

In a spare cell in row 4 say AA4

=TRIM(D4)
fill down as far as you need say in this case row 400

Copy AA4:AA400

Select Cell D4

PasteSpecial > values

Column AA Clear all

This will give you a temporary fix and proove your formulae

Best to fix this problem at source now you know where it comes from.

Cheers

EDIT
To late AGAIN
Why do I never see replies when I refresh my browser!!!

[johncena]

dear marco i have try this macro & it's given correct answer for me.thanks lot again.....

Please Login or Register  to view this content.

[Marcol]

As an after thought

The TRIM() formula in Excel will remove leading, trailing and any extra spaces

The Trim() formula in VBa only removes leading & trailing spaces

This might be worth watching if you use VBa to correct column D
There might be mismatches where accidental spaces have been added at source.

Cheers
Alistair

[Marcol]

In view of the above it might be better to use

Please Login or Register  to view this content.

Hope this helps

[johncena]

thanks for your macro Marcol.REP+