appending a Rows.count into a formula when the rows.count is from a diff worksheet

I need to create a macro which reads a row count from one worksheet and have it append to a vlookup formula range in a different worksheet.

For example, my formula would be
"vlookup('cur'!c3,'prior'!c$3:AJ$896,1,false)" <-- the 896 value is the value I want to replace with the row count from a different worksheet column.

Not sure how to go about this: confused:

Hi Mcg,

If you have no data below row 896 (or your last used row in the lookup table), you could just leave off the row numbers, e.g.

VLOOKUP('Cur'!C3,'Prior'!C:AJ,1,0)

If you truly need the last row, you could use:

Please Login or Register  to view this content.

Also note that setting the third argument of VLOOKUP to 1 will simply return the matched value, or an error if that value isn't found. Alternatively you could use MATCH or ISNUMBER(MATCH.

Hi Paul,

I am so confused at the moment. I understand what you are saying, but I am having a difficult time incorporating it. The reason I say this is because, I trying to incorporate your logic into the logic which is produced when I record the macro. The recorded macro logic is not very straight forward, because this is the logic it creates:

ActiveCell.FormulaR1C1 = _
"=IF(ISNA(VLOOKUP('Cur Pool'!RC,'Prior Pool'!R3C:R896C[33],1,FALSE)),""1"",""0"")"
Range("C3").Select
Selection.AutoFill Destination:=Range("C3:C896")

I am not sure how to incorporate your logic in replacing the 896 value with the ROWS.COUNT logic ???????

Thx. Marie