Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
vLookUp. Works Manually but not by code.
I am using Excel 97.
This is how the macro works. I have a list of account numbers. To make the spreadsheet easy to read. I have a "database" of account numbers in column A and the company names in column B. The macro sorts the "database" in column A, then proceeds to retrieve the value in column B. For whatever reason, the vLookUp misses some values. But if I do it manually, it works. Has anyone seen a problem like this? Thanking you in advance Rich |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
vLookUp. Works Manually but not by code.
Hi
you may post the code you have used -- Regards Frank Kabel Frankfurt, Germany schrieb im Newsbeitrag ups.com... I am using Excel 97. This is how the macro works. I have a list of account numbers. To make the spreadsheet easy to read. I have a "database" of account numbers in column A and the company names in column B. The macro sorts the "database" in column A, then proceeds to retrieve the value in column B. For whatever reason, the vLookUp misses some values. But if I do it manually, it works. Has anyone seen a problem like this? Thanking you in advance Rich |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
vLookUp. Works Manually but not by code.
Your code worked kind of ok for me.
It plopped in valid formulas, but when I looked at the worksheet I saw #n/a's. If I just selected the cell, did F2 and enter, the formula evaluated ok. (I've never seen this in real life--but I don't link between workbooks that often.) But I added a line to calculate and then everything looked ok: Option Explicit Sub testme() Dim lnRowCount As Long Dim lcLookUp As Long Dim lcFill As Long lcFill = 6 lnRowCount = 2 lcLookUp = 80 Do While lnRowCount < lcFill lnRowCount = lnRowCount + 1 ActiveSheet.Range("P" & lnRowCount).FormulaR1C1 = _ "=VLOOKUP(RC[-5],[RL_Macro.xls]Sheet1!R10C1:R" _ & lcLookUp & "C2,2,FALSE)" Loop Application.Calculate End Sub I got rid of the select and trim(str()) stuff. Excel is very forgiving. Rich wrote: Below is what the code looks like. lnRowCount = 2 Do While lnRowCount < lcFill lnRowCount = lnRowCount + 1 ActiveSheet.Range("P" & Trim(Str(lnRowCount))).Select ActiveCell.FormulaR1C1 = _ "=VLOOKUP(RC[-5],[RL_Macro.xls]Sheet1!R10C1:R" & lcLookUp & "C2,2,FALSE)" Loop "Frank Kabel" wrote in message ... Hi you may post the code you have used -- Regards Frank Kabel Frankfurt, Germany schrieb im Newsbeitrag ups.com... I am using Excel 97. This is how the macro works. I have a list of account numbers. To make the spreadsheet easy to read. I have a "database" of account numbers in column A and the company names in column B. The macro sorts the "database" in column A, then proceeds to retrieve the value in column B. For whatever reason, the vLookUp misses some values. But if I do it manually, it works. Has anyone seen a problem like this? Thanking you in advance Rich -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLookup not finding values unless lookup value is manually entered | Excel Worksheet Functions | |||
VLOOKUP only works on data manually entered. | Excel Discussion (Misc queries) | |||
manually specifing a range in the code below | Excel Programming | |||
Why won't this code works | Excel Programming | |||
VBA Code Works in 97, Not in 2002 | Excel Programming |