The VLookup does not reference the Variable properly...it trys to use the
"literal" name either the variable name or I even tried ActiveSheet.Name so
it returns an error message

"Bob Phillips" wrote:

INDIRECT is my oldest least favourite function, because it is so
inefficient.

Couldn't you use

vardisclose.Formula = "=VLookup("address" & ActiveSheet.Name & ",
disclosesheet, 3, False)"


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Debbie Horner" wrote in message
...
Hi Bob,

Thanks for the reply...Indirect is my new favorite function in excel. I
have enclosd the macro I am trying to do

I have several worksheets with named ranges (because I am using indirect
so
much).. I am trying to write a macro to fill certain cells with vlookup
information (which is all the same vlookup on different sheets) but the
named
ranges are different on each worksheet by a 3 digit suffix. for instance
addressOFD and address OPD...so I am trying to define the suffix based on
the
sheet name (using a lookup) and then select the range.. do you think
Indirect
could work here?

Sub InsertGaragingAddress()

Dim vardisclose As String

vardisclose = ("=VLookup(ActiveSheet.Name, disclosesheet, 3, False)")


Range(GAddress1 + vardisclose).Select
ActiveCell.Value = "=VLookup(customer, customers, 6, False)"

End Sub


"Bob Phillips" wrote:

=INDIRECT("myVar"&VLOOKUP("ABC,myTable,B2:H10,3,Fa lse)

as an example

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"Debbie Horner" wrote in message
...
Hi-
I would like to create a variable based on a vlookup value and the I
want
to
concantenate some text and the variable to define a range name. I am
having
trouble defining the variable as...what string is not correct...I am a
total
rooky at this but learning fast.

Thanks

Debbie