Excel 2003 - VBA - Sheet Referencing in Formulas

Giganews Newsgroups
Subject: Excel 2003 - VBA - Sheet Referencing in Formulas
Posted by:  Craig Brandt (brandtcra…@att.net)
Date: Wed, 2 Jul 2008

Hi Guys:

I'm drawing a blank and need some help.

I have a table with row 3 containing sheet names (currently pointing at 106
sheets).

In rows 4-250 I want to put formulas that will look for something (located
in Column B) in each of the sheets named in row 3. The first cell in the
target sheet contains the last row with valid data.

Example:

Cell "D3" = P02-05

Cell "B5" = 123456789IBM

In cell "D5" I want to generate a formula that will look in sheet P02-05,
search for the contents of "B5" throughout the sheet and return the Value in
column 8.

Cell "D5" should be =Vlookup($B5,'P02-05'!$A$2:$H$1080,8,0)

My thinking on VBA Code :

Cells(5,4).formulaR1C1 = "=Vlookup(RC2,'" & ^^^ & "'!R2C1:R"  & ^^^ &
"C8,8,0)"

I'm having difficulty figuring out how to pull the Sheet name then the
Number of Lines from the target sheet ("A1" in the target sheet).

Any help would be appreciated,
Thanks,
Craig

Replies