vlookup errors
Posted by Jim on August 08, 2001 8:26 AM
I have two tables that share have the same information
in one column in each. Periodically, the vlookup command
will not find the "lookup value" and return and error.
Sometimes when that happens, all I have to do is retype
the value in the "lookup value" column and then it works.
Is there a way to get around retyping the value to get
the formula to work?
Posted by Cory on August 08, 2001 10:04 AM
It sounds like the sheet to recalculate itself before the formulas work. When you change the value of a cell (type in the value again to get it to work) the sheet recalculates itself. You can manually do this by pressing F9 or do it through the VBE using:
All open workbooks: Application.Calculate (or just Calculate)
A specific worksheet: Worksheets(1).Calculate
A specified range: Worksheets(1).Rows(2).Calculate
That help?
Cory
Posted by Jim on August 08, 2001 10:11 AM
text vs. Value
No, but I did find that when the vlookup was not
working, the number in the cell was being treated
text. I used the =value() command in a new column and
then copied the numbers all back and then it worked.
Is there a way to tell the cells to treat the numbers
as values instead of text?
Posted by Cory on August 08, 2001 10:19 AM
Re: text vs. Value
You could format the cells as "numbers" (highlight and right-click the cells, Format Cells, and under the number tab select "number"...), if you haven't already. I've noticed a bug that sometimes when cell values are changed, excel will convert them to text for no apparent reason... I don't know how to get past that one unless you work the Value function into your present formula...
Cory
Posted by Aladin Akyurek on August 08, 2001 11:19 AM
Or...
coerce Excel/VLOOKUP to see numbers even if it is fed a text formatted number by using +0 as in
=VLOOKUP(A1+0,Table,IndexColumn,MatchType)
Aladin
Posted by Cory on August 08, 2001 12:19 PM
Cool! thanks Aladin!
I didn't even know you could do that. I'm still a beginner (close to intermediate) at excel and vba; there's so much to learn! But I'm getting a little better everyday... I try and answer every question I possibly can, not just to learn more, but I'd feel guilty asking questions all of the time if I never gave anything in return...
Cory
Posted by Aladin Akyurek on August 08, 2001 12:37 PM
Cory: Could you post that last search tip again?
Forgat to bookmark it.
Thanks.
Aladin
Posted by Mark W. on August 08, 2001 1:49 PM
Do you mean...
http://www.alltheweb.com/index.php?cat=web&lang=english&query=
Posted by Aladin Akyurek on August 08, 2001 2:00 PM
Yeah.
Posted by Ian on August 08, 2001 2:16 PM
just checked that link cory..cool
