Taking a cell value to use in namerange lookup?

[wwgmr]

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!

 

[ETID]

Something like this maybe?

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

 

[SkipVought]

ww,

This statement

if Value is [newname] then

will only work IF newname is a single cell range name.

Your code example

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

"JaneName"
[code]
Is THAT what you want for your lookup value?

Skip,
Skip@TheOfficeExperts.com
[URL unfurl="true"]www.TheOfficeExperts.com[/URL]

 

[wwgmr]

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!