View Single Post
  #6   Report Post  
Posted to microsoft.public.excel.misc
Andy Pope Andy Pope is offline
external usenet poster
 
Posts: 2,489
Default Indirect Addressing in VBA

Hi,

Not sure it simple. But I guess 1 approach would be to add code to the
workbook Open event to check and then open the workbook if required.

Private Sub Workbook_Open()

Dim wbkRef As Workbook

On Error Resume Next
Set wbkRef = Workbooks("Trades Sheet 7-2-07.xls")
If wbkRef Is Nothing Then
Set wbkRef = Workbooks.Open("C:\Trades Sheet 7-2-07.xls")
If wbkRef Is Nothing Then
MsgBox "Unable to open referenced workbook", vbExclamation
End If
End If
If StrComp(wbkRef.FullName, _
"C:\Trades Sheet 7-2-07.xls", vbTextCompare) < 0 Then
MsgBox "File with same name but diffrent location" & _
" is already open", vbExclamation
Else
' All is well
MsgBox "OK"
End If

End Sub

Cheers
Andy

C Brandt wrote:
Andy:

Thanks for the response.
You were right. I didn't have the target sheet open. Now I have to figure
out why it is accessing the wrong cell.

Is there a simple VBA command that will ensure that the target file is
open?

Regards,

Craig


"Andy Pope" wrote in message
...

Hi,

INDIRECT references will only work if the referenced workbook is open.
Otherwise it will return #REF.

Is Trades Sheet 7-2-07.xls open?

Cheers
Andy

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"C Brandt" wrote in message
. ..

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