View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.misc
C Brandt C Brandt is offline
external usenet poster
 
Posts: 92
Default Indirect Addressing in VBA

Hi Guys:

Indirect addressing looks to be the soultion to another problem I'm having,
but for the life of me, I cannot seem to get it working, which of course
means I don't understand it. Documentation seems very weak in this area.

I would like to do a vlookup into another worksheet, but the name of the
worksheet varies from day to day. Furthermore, since I do not want this link
to be active in the final product, I create the formula using a button
driven macro that ends it's function by copy, then paste-value of all the
formulas.

To simplify this discussion I would like to replace the VLOOKUP with a
simple Equal. If I were to hard code it, this is what the formula would look
like:
='[Trades Sheet 7-2-07.xls]Buy'!$C$363
This is what I put in there to replace the formula
Cell I4 = Trades Sheet 7-2-07.xls
Cell I5 = Buy'!$C$363
And the formula that will be pasted down the row is:
Cell I8 = =INDIRECT("'["&I4&"]"&I5)
This results in a #REF!.

Any clues?

Craig