set a variable based on a vlookup

D

Debbie Horner

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
 
B

Bob Phillips

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

as an example

--
HTH

Bob

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

Debbie Horner

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
 
B

Bob Phillips

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

Debbie Horner

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
 
B

Bob Phillips

Which variable name are you referring to?

--
HTH

Bob

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