A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Indirect Addressing in VBA



 
 
Thread Tools Display Modes
  #1  
Old July 3rd 07, 03:25 PM posted to microsoft.public.excel.misc
C Brandt
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


Ads
  #2  
Old July 3rd 07, 03:40 PM posted to microsoft.public.excel.misc
Andy Pope
external usenet poster
 
Posts: 2,489
Default Indirect Addressing in VBA

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
>
>


  #3  
Old July 3rd 07, 03:55 PM posted to microsoft.public.excel.misc
Earl Kiosterud
external usenet poster
 
Posts: 611
Default Indirect Addressing in VBA

Craig,

The target workbook, Trades Sheet 7-2-07.xls, must be open. Indirect doesn't work with
closed files. If you're doing this via a macro, then make a link to the data instead using
INDIRECT. This should work with the target workbook closed.

='[Trades Sheet 7-2-07.xls]Buy'!$C$363

--
Earl Kiosterud
www.smokeylake.com

Note: Top-posting has been the norm here.
Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...
-----------------------------------------------------------------------
"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
>
>



  #4  
Old July 3rd 07, 04:11 PM posted to microsoft.public.excel.misc
C Brandt
external usenet poster
 
Posts: 92
Default Indirect Addressing in VBA

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
> >
> >

>



  #5  
Old July 3rd 07, 04:36 PM posted to microsoft.public.excel.misc
Dave Peterson
external usenet poster
 
Posts: 35,220
Default Indirect Addressing in VBA

Just to add to Earl's suggestion...

You may want to specify the drive/folder that contains that workbook, too.



Earl Kiosterud wrote:
>
> Craig,
>
> The target workbook, Trades Sheet 7-2-07.xls, must be open. Indirect doesn't work with
> closed files. If you're doing this via a macro, then make a link to the data instead using
> INDIRECT. This should work with the target workbook closed.
>
> ='[Trades Sheet 7-2-07.xls]Buy'!$C$363
>
> --
> Earl Kiosterud
> www.smokeylake.com
>
> Note: Top-posting has been the norm here.
> Some folks prefer bottom-posting.
> But if you bottom-post to a reply that's
> already top-posted, the thread gets messy.
> When in Rome...
> -----------------------------------------------------------------------
> "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
> >
> >


--

Dave Peterson
  #6  
Old July 3rd 07, 08:06 PM posted to microsoft.public.excel.misc
Andy Pope
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
>>>
>>>

>>

>
>

  #7  
Old July 3rd 07, 08:40 PM posted to microsoft.public.excel.misc
C Brandt
external usenet poster
 
Posts: 92
Default Indirect Addressing in VBA

Thanks All:

Maybe I am attacking this all wrong.

Key features of the task a
1) I want to generate a way to grab the data from another spread
sheet(target file) on demand only
2) I do not want active links, since that target file changes
3) Everyone that will be using this feature does not have the same directory
structure
4) I can require everyone to keep the target file in the save directory as
the running spreadsheet

The following is the way I currently do it.
I input the following formula in cell I8 of the running spreadsheet
"=Vlookup($H8,'[Trades Sheet 7-2-07.xls]Buy'!$A$13:$BV$363,70,0)"
Manually copy this formula down to around cell I500
Copy I8:I500
Paste-value in cells I8:I500
Save and distribute the file.

When I am done, no links exist, and all would be great, except the peope
that use the running spreadsheet make minor changes to the sheet to suite
their own needs and would like to do the update on their own.

Is there a simple way of inputting the name of the Target File (Trade Sheet
7-2-07.xls) in a cell location in the running spreadsheet and via a button
executed macro, create that formula, then copy-paste it to eliminate the
link?

I thought Indirect was the best solution, but I'm not so sure now.

Craig



"Earl Kiosterud" > wrote in message
...
> Craig,
>
> The target workbook, Trades Sheet 7-2-07.xls, must be open. Indirect

doesn't work with
> closed files. If you're doing this via a macro, then make a link to the

data instead using
> INDIRECT. This should work with the target workbook closed.
>
> ='[Trades Sheet 7-2-07.xls]Buy'!$C$363
>
> --
> Earl Kiosterud
> www.smokeylake.com
>
> Note: Top-posting has been the norm here.
> Some folks prefer bottom-posting.
> But if you bottom-post to a reply that's
> already top-posted, the thread gets messy.
> When in Rome...
> -----------------------------------------------------------------------
> "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
> >
> >

>
>



  #8  
Old July 3rd 07, 10:13 PM posted to microsoft.public.excel.misc
Dave Peterson
external usenet poster
 
Posts: 35,220
Default Indirect Addressing in VBA

You know that the formula's syntax has to be something like:

=Vlookup($H8,'[Trades Sheet 7-2-07.xls]Buy'!$A$13:$BV$363,70,0)
or when that file is closed:
=VLOOKUP(H8,'C:\My Documents\excel\[[Trades Sheet 7-2-07.xls]Buy'!$A:$CQ,70,0)

So you could get the filename--application.getopenfilename may work nicely if
you allow the users to put the file whereever they want.

Or you could pick it up from a cell--but be aware that you'll have to do some
validation for this to work right--make sure the user gives a correct location
and filename. Here's hoping that the worksheets keep the name Buy(!).


Option Explicit
Sub testme()

Dim WkbkName As String
Dim FolderName As String
Dim myStr As String
Dim myRng As Range
Dim myCell As Range

With ActiveWorkbook.Worksheets("sheet1")
Set myCell = .Range("a1")
WkbkName = myCell.Value

FolderName = "C:\My Documents\excel\"

myStr = "'" & FolderName & "[" & WkbkName & "]buy'!$a$13:$bv$363"

Set myRng = .Range("I8:I" & .Cells(.Rows.Count, "A").End(xlUp).Row)

End With

With myRng
.Formula = "=vlookup($h8," & myStr & ",70,0)"
.Value = .Value
End With

End Sub

I filled column I from row 8 to the last row that was used in column A.



C Brandt wrote:
>
> Thanks All:
>
> Maybe I am attacking this all wrong.
>
> Key features of the task a
> 1) I want to generate a way to grab the data from another spread
> sheet(target file) on demand only
> 2) I do not want active links, since that target file changes
> 3) Everyone that will be using this feature does not have the same directory
> structure
> 4) I can require everyone to keep the target file in the save directory as
> the running spreadsheet
>
> The following is the way I currently do it.
> I input the following formula in cell I8 of the running spreadsheet
> "=Vlookup($H8,'[Trades Sheet 7-2-07.xls]Buy'!$A$13:$BV$363,70,0)"
> Manually copy this formula down to around cell I500
> Copy I8:I500
> Paste-value in cells I8:I500
> Save and distribute the file.
>
> When I am done, no links exist, and all would be great, except the peope
> that use the running spreadsheet make minor changes to the sheet to suite
> their own needs and would like to do the update on their own.
>
> Is there a simple way of inputting the name of the Target File (Trade Sheet
> 7-2-07.xls) in a cell location in the running spreadsheet and via a button
> executed macro, create that formula, then copy-paste it to eliminate the
> link?
>
> I thought Indirect was the best solution, but I'm not so sure now.
>
> Craig
>
> "Earl Kiosterud" > wrote in message
> ...
> > Craig,
> >
> > The target workbook, Trades Sheet 7-2-07.xls, must be open. Indirect

> doesn't work with
> > closed files. If you're doing this via a macro, then make a link to the

> data instead using
> > INDIRECT. This should work with the target workbook closed.
> >
> > ='[Trades Sheet 7-2-07.xls]Buy'!$C$363
> >
> > --
> > Earl Kiosterud
> > www.smokeylake.com
> >
> > Note: Top-posting has been the norm here.
> > Some folks prefer bottom-posting.
> > But if you bottom-post to a reply that's
> > already top-posted, the thread gets messy.
> > When in Rome...
> > -----------------------------------------------------------------------
> > "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
> > >
> > >

> >
> >


--

Dave Peterson
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Cell Addressing John Calder New Users to Excel 3 March 9th 07 12:32 PM
Cell addressing Mike Excel Worksheet Functions 0 November 30th 06 02:51 PM
Indirect Addressing Kanga 85 Excel Discussion (Misc queries) 3 December 5th 05 04:03 AM
Sorksheet addressing (I think) in '97 Adam Kroger Excel Discussion (Misc queries) 0 November 27th 05 10:29 PM
Allow EXCEL INDIRECT(ADDRESS()) and INDIRECT(RANGE()) functions Mike Barlow Excel Worksheet Functions 6 July 6th 05 03:04 AM


All times are GMT +1. The time now is 11:30 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.