Lookup headache

[dustyduncan]

I have a set of values in Sheet1 that I need to pull data for each
value from Sheet2. I've tried a simple VLOOKUP, and it will not work,
no way, no how. Yes, the lookup table is sorted ascendingly. I get
errors on about 85% of my rows when I copy the formula down.
Yet, when I copy the value from Sheet1 and paste into the Find and
Replace box to search Sheet2, it finds the value everytime.
What I need is a macro that will will copy and paste the corresponding
data from Sheet2 into Sheet1 in column I.
My lookup value is in Sheet 1, Column C. The lookup table will be
Sheet2! A:B, with the value I need to copy and paste to Sheet1 in
column B of Sheet2.

There has to be a simple set of code to do this--I'm desperate--please
help!

dutty

 

[Randy Harmelink]

Yet, when I copy the value from Sheet1 and paste into the Find and Replace box to search Sheet2, it finds the value everytime. <<

Is it possible your VLOOKUP() key field in the table is a string value
with trailing blanks? That would allow the F&R to work, but give you a
non-match on a VLOOKUP().

 

[Dutty]

I tested that using a "Text to Columns" run, and got the same result.
Also did a =VALUE() into a different cell.......and same result? Ergo,
I don't think I have trailing blanks given that. Is there another way
to test?

 

[idyllicabyss]

Here's what I do:

use Find and replace to search for " " (two spaces) and replace with
nothing, ie, don't put anything in the replace with box.
repeat that until you get an error with nothing to change.

then create a quick macro to clean up any odd single spaces on the end

sub CleanSpaces()
for each cell in sheets("Sheet2").range("A:B")
if right(cell,1) = " " then cell = left(cell,len(cell)-1)
next cell

you can more precisely define your A:B range to speed things up, ,eg
"A1:B180" saves the macro doing 65000 rows.

To check if you have trailing spaces in the first place you can simply
click in the cell and click into the edit box well after the last
proper character.

 

[Randy Harmelink]

If you are getting the data from the web, sometimes "non-breaking
spaces" inserted by HTML can cause all kinds of problems, even though
they can't be seen.

This may be a situation where looking at the actual data will answer
the question in a few seconds. Can you edit out a subset of the data
that can be viewed and make it accessible from the web?

 

[Dutty]

The spacing of the data in the column B presents a problem with F&R, as
there are spaces in it.
I can provide a subset of data, how do you recommend making it
accessible.......I've never done that before?

I appreciate your help.