Define an Offset Address as a Range

Hi all,

I have been unsuccessfully trying to populate filtered column AT with v-lookup formulas, starting with the first visible cell below the header. With the code below, I know that the first visible row is 6461. After that, I created the variable Lookref to contain the address 4 cells to the right in column AX, and it evaluates to "$AX$6461". I attempted to use that variable value in the Vlookup formula, but it doesn't work properly, returning #NAME?. Also, I couldn't DIM & SET Lookref as a Range as I originally intended, that's why I commented it out. I was getting the message "Type Mismatch" and it was highlighting the ".Address".


So the intent is to populate column AT with a vlookup of column AX into worksheet file "PrevFedITR" which is in the same workbook as the vlookup formula. How can I get "Lookref" to work and also have the code populate the column by moving the formula and the Lookref to move down a row-at-a-time?

Thanks.

Brian

Please Login or Register  to view this content.

1- Lookref isn't a range .. it should return a String eg.$A$1
2- This will produce a Lookref as you typed it in the cell formula.

Use this instead of last line of the code:
ActiveCell.Formula = "=VLOOKUP(" & Lookref & ",PrevFedITR!$AT$2:$AU$50000,2,FALSE)"