set a variable based on a vlookup
 

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

set a variable based on a vlookup
 

=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

set a variable based on a vlookup
 

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

set a variable based on a vlookup
 

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

set a variable based on a vlookup
 

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

set a variable based on a vlookup
 

Which variable name are you referring to?

--
HTH

Bob

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

"Debbie Horner" wrote in message
...
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