Vlookup with indirect function to vba

[nhinx]

Hi everyone,

I have a "Summary" sheet and other sheets namely "Sheet A", "Sheet B", "Sheet C", "Sheet D", "Sheet E", "Sheet F", "Sheet G", "Sheet H", "Sheet I". In those sheets from "Sheet A" to "Sheet I" has the word "Grand Total" at the bottom in column B which varies in location due to different sets of data. Beside the word Grand Total is the total amount in Column C. While in "Summary" sheet collates the Grand Total from "Sheet A" to "Sheet I". In the Summary sheet has the following data:

A2 = "Sheet A" in C2=VLOOKUP("Grand Total",INDIRECT("'"&A2&"'!b:c"),2,0)
A4 = "Sheet B" in C4=VLOOKUP("Grand Total",INDIRECT("'"&A4&"'!b:c"),2,0)
A6 = "Sheet C" in C6=VLOOKUP("Grand Total",INDIRECT("'"&A6&"'!b:c"),2,0)
A8 = "Sheet D" in C8=VLOOKUP("Grand Total",INDIRECT("'"&A8&"'!b:c"),2,0)
A10 = "Sheet E" in C10=VLOOKUP("Grand Total",INDIRECT("'"&A10&"'!b:c"),2,0)
A12 = "Sheet F" in C12=VLOOKUP("Grand Total",INDIRECT("'"&A12&"'!b:c"),2,0)
A14 = "Sheet G" in C14=VLOOKUP("Grand Total",INDIRECT("'"&A14&"'!b:c"),2,0)
A16 = "Sheet H" in C16=VLOOKUP("Grand Total",INDIRECT("'"&A16&"'!b:c"),2,0)
A18 = "Sheet I" in C18=VLOOKUP("Grand Total",INDIRECT("'"&A18&"'!b:c"),2,0)

the formula in column C works fine as worksheet function which gathers the Grand Total for every sheets. Can you help me do a VBA code for this so that I will no longer copy those formula in the next row if there will be additional sheets like "Sheet J" to "Sheet O"

Thanks in advance,
Nhinx

 

[Ells_]

So you basically want to use VBA instead of just using a formula that you can basically just drag down? Seems kind of like a waste of VBA. In general, it's best to only use VBA for things you couldn't otherwise do in a formula.

Here's a way to make A2...A18 a formula that you can drag down:

="Sheet "&CHAR(64+FLOOR.MATH(ROW()/2))

 

[nhinx]

Hi Ells_,

Thanks for the code. There are other concerns which are related for the Vlookup formula above this is just my sample. I just don't know how to make the Indirect works in VBA since it was not classified as worksheet function in VBA.

Many thanks,

Nhinx