Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup headache
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup headache
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(). |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup headache
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup headache
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup headache
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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup headache
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup Headache | Excel Worksheet Functions | |||
Hyperlink Headache | Excel Programming | |||
Hyperlink Headache | Excel Programming | |||
IF formula headache | Excel Programming | |||
Look up Headache | Excel Programming |