Find defined Name in List, then Goto
 

Hello again
Still playing with several different codes. I thought I understood this one,
but I guess I was wrong.
This code selects the whole range ( $A$66:$A$2020) and not the Name found in
$A$4 ( defined as Supplier)
I've tried using the defined names as well as the cell addresses, nothing
worked so far.
It is suppose to look at $A$4, find that name in the range ( $A$66:$A$2020)
defined as ListSup, then go there so that you can select the Hyperlink next
to it and view certain certificates.




Sub ViewCert()

Dim rngToSearch As Range
Dim rngFound As Range
Dim wks As Worksheet

Set wks = ActiveSheet
Set rngToSearch = Worksheets("Certs").Range("$A$66:$A$2020")
Set rngFound = rngToSearch.Find("$A$4")

Application.Goto rngToSearch, True


End Sub

Also tried this code, but it gives me an error and I do not know if it is
the '.Value in the line:
myFind = Worksheets("Certs").Range("Supplier").Value
since it is not numeric, but text
??????
stumped€¦..

Any help is appreciated.Thanks

Sub TakeMeThere()
Dim myFind As Integer
Dim rng As Range
myFind = Worksheets("Certs").Range("Supplier").Value
Set rng = Worksheets("Certs").Range( _
"ListSup").Find(myFind, _
LookIn:=xlValues, Lookat:=xlWhole)

If Not rng Is Nothing Then
Application.Goto rng, True

Else
MsgBox myFind & " was not found"
End If

End Sub

Find defined Name in List, then Goto
 

Hi,
Try this:

Sub ViewCert()

Dim rngToSearch As Range
Dim rngFound As Range
Dim wks As Worksheet

Set wks = ActiveSheet
Set rngToSearch = Worksheets("Sheet1").Range("$A$66:$A$2020")
Set rngFound = rngToSearch.Find(Range("$A$4"))

If Not rngFound Is Nothing Then
Application.Goto Range(rngFound.Address), True
End If




End Sub

"ufo_pilot" wrote:

Hello again
Still playing with several different codes. I thought I understood this one,
but I guess I was wrong.
This code selects the whole range ( $A$66:$A$2020) and not the Name found in
$A$4 ( defined as Supplier)
I've tried using the defined names as well as the cell addresses, nothing
worked so far.
It is suppose to look at $A$4, find that name in the range ( $A$66:$A$2020)
defined as ListSup, then go there so that you can select the Hyperlink next
to it and view certain certificates.




Sub ViewCert()

Dim rngToSearch As Range
Dim rngFound As Range
Dim wks As Worksheet

Set wks = ActiveSheet
Set rngToSearch = Worksheets("Certs").Range("$A$66:$A$2020")
Set rngFound = rngToSearch.Find("$A$4")

Application.Goto rngToSearch, True


End Sub

Also tried this code, but it gives me an error and I do not know if it is
the '.Value in the line:
myFind = Worksheets("Certs").Range("Supplier").Value
since it is not numeric, but text
??????
stumped€¦..

Any help is appreciated.Thanks

Sub TakeMeThere()
Dim myFind As Integer
Dim rng As Range
myFind = Worksheets("Certs").Range("Supplier").Value
Set rng = Worksheets("Certs").Range( _
"ListSup").Find(myFind, _
LookIn:=xlValues, Lookat:=xlWhole)

If Not rng Is Nothing Then
Application.Goto rng, True

Else
MsgBox myFind & " was not found"
End If

End Sub

Find defined Name in List, then Goto
 

Thanks Toppers,
This worked great for me . I also realized that I continually used the name
'Cert' for my "sheet1", but it was actually named 'Certs'
That makes me wonder if maybe one of the 12 different codes I tried did not
work...
Have a safe and happy Holiday Season, whatever your faith may be.

"Toppers" wrote:

Hi,
Try this:

Sub ViewCert()

Dim rngToSearch As Range
Dim rngFound As Range
Dim wks As Worksheet

Set wks = ActiveSheet
Set rngToSearch = Worksheets("Sheet1").Range("$A$66:$A$2020")
Set rngFound = rngToSearch.Find(Range("$A$4"))

If Not rngFound Is Nothing Then
Application.Goto Range(rngFound.Address), True
End If




End Sub

"ufo_pilot" wrote:

Hello again
Still playing with several different codes. I thought I understood this one,
but I guess I was wrong.
This code selects the whole range ( $A$66:$A$2020) and not the Name found in
$A$4 ( defined as Supplier)
I've tried using the defined names as well as the cell addresses, nothing
worked so far.
It is suppose to look at $A$4, find that name in the range ( $A$66:$A$2020)
defined as ListSup, then go there so that you can select the Hyperlink next
to it and view certain certificates.




Sub ViewCert()

Dim rngToSearch As Range
Dim rngFound As Range
Dim wks As Worksheet

Set wks = ActiveSheet
Set rngToSearch = Worksheets("Certs").Range("$A$66:$A$2020")
Set rngFound = rngToSearch.Find("$A$4")

Application.Goto rngToSearch, True


End Sub

Also tried this code, but it gives me an error and I do not know if it is
the '.Value in the line:
myFind = Worksheets("Certs").Range("Supplier").Value
since it is not numeric, but text
??????
stumped€¦..

Any help is appreciated.Thanks

Sub TakeMeThere()
Dim myFind As Integer
Dim rng As Range
myFind = Worksheets("Certs").Range("Supplier").Value
Set rng = Worksheets("Certs").Range( _
"ListSup").Find(myFind, _
LookIn:=xlValues, Lookat:=xlWhole)

If Not rng Is Nothing Then
Application.Goto rng, True

Else
MsgBox myFind & " was not found"
End If

End Sub