PDA

View Full Version : Solved: Find a named range and then a value within



pcarmour
12-18-2012, 01:18 PM
Hi,
I have a spread sheet with a number of named ranges(all different sizes), within each of the named ranges there is a variety of data.
I have the name of the named range I want to find and the value of the data I want to find in two cells on the same sheet.
I now want a code that will find the named range from the name in cell 1 and then find the value within that range from the value in cell 2.

I am working with Windows Home Premium version 6.1.7601 SP 1 Build7601and Excel version 14.0.6123.5001 (32 bit)
Any help, as always, is very much appreciated.

omnibuster
12-18-2012, 01:49 PM
Try this. (If i correct understanding).


Sub NamedR()
Range("A1").Select
Activecell.value=”123”
ActiveCell.Name = "Google"
Range("C221").Select
Application.Goto Reference:="Google"
'OR
'Range("Google").Select ' or Activate
MsgBox Range("Google").Value
End Sub

pcarmour
12-18-2012, 02:03 PM
Hi, Thank you for getting back so promptly.
I'm sorry but your code is not what I'm looking for.
The various ranges are already named, so with the value (which is the same name as one of the named ranges) in cell 1 I search for that named range and then with the value in cell 2 I search/go to the value within the selected named range.

For example: Say there are 3 named ranges Tom, Dick and Harry. Cell 1 has value Dick. cell 2 has value ABC123.
So I want to go to the Dick range and then within the Dick range I want to go to value ABC123.
I hope that is clearer.

Teeroy
12-18-2012, 08:10 PM
Peter,

The following should do it. The Named Range is in A1 and the cell value is in B1.

Sub Goto_Cell_in_Named_Range()
Sheets(Names([A1]).RefersToRange.Parent.Name).Select
Range([A1]).Find(What:=[B1], LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
End Sub

pcarmour
12-19-2012, 02:59 AM
Hi Teeroy,
Your code looks exactly what I am looking for but can you please let me know what definitions I should use.
I added Dim Holdings As Worksheet (Holdings is the name of my worksheet) but now get "Object variable or With block variable not set (Error 91)"
Thanks in advance for your help.

Teeroy
12-19-2012, 03:30 AM
There are no variables in the code so you shouldn't need to Dim anything. Can you post the code you tried?

pcarmour
12-19-2012, 05:52 AM
Hi, It's basically your code adjusted for my Holdings spreadsheet.

Sub Goto_Cell_in_Named_Range()
Dim Holdings As Worksheet

Sheets(Holdings([F46]).RefersToRange.Parent.Name).Select
Range([F46]).Find(What:=[G46], LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
End Sub

Bob Phillips
12-19-2012, 06:40 AM
Try this

Sub Goto_Cell_in_Named_Range()

With Worksheets("Holdings")

.Range(.Range("F46").Value).Find(What:=.Range("G46").Value, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False).Activate
End With
End Sub

pcarmour
12-19-2012, 08:09 AM
HI xld, Brilliant, exactly the code needed.
Thank you very much.

Teeroy
12-19-2012, 01:02 PM
Peter,

FYI your sample had holdings as an object which wasn't defined, not the name of a worksheet.

pcarmour
12-19-2012, 01:47 PM
Hi Teeroy,
Thank you for explaining, I can see what you mean, I'm learning all the time.
Thanks again for your help.