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

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

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