VLOOKUP macro needs Offset

Posted by School Marm on June 08, 2001 2:13 PM

Here is my situation: I'm putting together an attendance sheet and have named a sheet for each teacher. When I come to the sheet named "Attendance" the third column is the resultant number of a bar code scan. Each scan has a matching code in a table on that page, i.e., 123445 = Present.

My formula is coming from the teacher's page [Sheets(shName).Activate] and seeks to execute the following:

ActiveCell = "=VLOOKUP(myLookup,Legend,2,FALSE)"

This all works fine execpt the ActiveCell is in Row 3 and the first reference in myLookup is in Row 2. I have been looking diligently for documentation on VLOOKUP and Visual Basic but can find little. Can anyone help?

Thanks in advance!

Posted by Barrie Davidson on June 08, 2001 2:27 PM

I am assuming that you want to offset the address of "myLookup" by one row. With that in mind, I used the following code:
Formula_String = Range("myLookup").Offset(1, 0).Address(, , xlA1)
Formula_String = "=VLOOKUP(" & Formula_String & ",Legend,2,FALSE)"
ActiveCell = Formula_String

where "Formula_String" is a string variable declared at the beginning of the macro.

Hope this helps,
Barrie

Posted by School Marm on June 09, 2001 8:34 AM

Barrie,

You are a godsend and I got the first part of your suggestion to work, but since this was all in a DO ... UNTIL loop, the absolute address I kept getting passed wouldn't work. So rather than using the Offset (which seems the most efficient way to me) I ended up with this:

Formula_String = Range("myLookup").Address
(rowAbsolute:=False, columnAbsolute:=False)
nextString = "Attendance!" & Formula_String
F_String = "=VLOOKUP(" & nextString & ",
Legend,2,FALSE)"
ActiveCell = F_String

Gosh, You folks working this board are super fast and very helpful. Thank you!