Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Taking a cell value to use in namerange lookup?

Status
Not open for further replies.

wwgmr

MIS
Mar 12, 2001
174
US
Hi all,

I wanted to find out if it is possible to take the value in a cell say c1 on a sheet. I even named that cell to try to make this easier. Name is First I want to take the Value for First and add another word to it to use to look up a value.

NewName = [First]&"Name"

I can then write something like this.

if Value is [newname] then
do something.

I just cannot seem to get how to use the name to do this. Is this possible?

Thanks!
 
Something like this maybe?

sub test()
dim x as string
x=application.worksheetfunction.vlookup(range("c1")&"Name",sheets("sheetx").range(lookup range),offsetvalue,false)
end sub
 
ww,

This statement
Code:
if Value is [newname] then
will only work IF newname is a single cell range name.

Your code example
Code:
NewName = [First]&"Name"
indicates that newname is ONLY A VARIABLE and NOT A RANGE NAME -- unless there's comething we don't know.

The other thing is that range name, [First] is ALSO a single cell by inference, and NOT a column range.

So if the VALUE in [First] is "Jane", then the value in NewName would be
Code:
"JaneName"
[code]
Is THAT what you want for your lookup value?

Skip,
Skip@TheOfficeExperts.com
[URL unfurl="true"]www.TheOfficeExperts.com[/URL]
 
Thanks both of you, Yes JaneName is what I would like because I will have arrays that will be named like JaneName SteveName and in my function I will have to search the different Named Ranges. So I wanted to be able to take the value from one cell that would be kept to one word no spaces. Add that to the Name text and create a new word that will be a NameRange. That way I can setup a Vlookup to search that range for a value from a different cell.

I have an understanding of how to do this in normal functions just not in VBA. I think that application.worksheetfunction.vlookup will work if we can create the use the new range name as the array I want to look in for the data.

Thanks again for the help!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top