vLookUp. Works Manually but not by code.

[richmarin]

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

 

[Frank Kabel]

Hi
you may post the code you have used

 

[Rich]

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

 

[Dave Peterson]

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.

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

Hi
you may post the code you have used