Relative sheet referencing in excel formulas
 

Hi,

I'm trying to write a macro that will import a sheet from another
workbook(say, X) and then add stuff to each of the rows by using a vlookup
formula that looks in the second worksheet of the original workbook (say, Y).
The first sheet in Y will have some rows that are different to those in sheet
2 in Y but will also have some of the same ones. For those that are the same,
the vlookup will return some comments from sheet 2.

The problem, however, is that the sheet names are always different because
they come from a file that is generated on a daily basis. Is it possible to
put a relative reference into the vlookup formula that will look on the
second sheet instead of sheet(...)?

Specifically, the formula in my macro looks like this:
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-16],0529080946!RC[-16]:R[1056]C,16,FALSE)"

and I am trying to change the "0529080946!" to sheets(2) in my workbook.

Thank you in advance for your help.

Michael

Relative sheet referencing in excel formulas
 

Sub qwerty()
Dim s As String
s = Sheets(2).Name & "!RC[-16]:R[1056]C,16,FALSE)"
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-16]," & s
End Sub

or something similar
--
Gary''s Student - gsnu200789


"MichaelR" wrote:

Hi,

I'm trying to write a macro that will import a sheet from another
workbook(say, X) and then add stuff to each of the rows by using a vlookup
formula that looks in the second worksheet of the original workbook (say, Y).
The first sheet in Y will have some rows that are different to those in sheet
2 in Y but will also have some of the same ones. For those that are the same,
the vlookup will return some comments from sheet 2.

The problem, however, is that the sheet names are always different because
they come from a file that is generated on a daily basis. Is it possible to
put a relative reference into the vlookup formula that will look on the
second sheet instead of sheet(...)?

Specifically, the formula in my macro looks like this:
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-16],0529080946!RC[-16]:R[1056]C,16,FALSE)"

and I am trying to change the "0529080946!" to sheets(2) in my workbook.

Thank you in advance for your help.

Michael