Searching data within sheets

[Barbara]

Hi,
I have a workbook with 2 worksheets. The 1st have anual client sales. The
2nd, all client information. both have a column with the client number.
I would like to be able to click on the client number of the anual sales and
be redirect to that specific client in the client information sheet.
How can I do that? I tried to format in the anual sales the clietn column
with hyperlink but didn't work.
Can someone give me a clue to solve this. Any help would be appreciate.
Thaks,
Barbara

 

[Gary''s Student]

Use:

Sub takemethere()
v = ActiveCell.Value
Set w = ActiveSheet
Sheets("details").Activate
n = Cells(Rows.Count, "C").End(xlUp).Row
For i = 1 To n
If Cells(i, "C").Value = v Then
Cells(i, "C").Select
Exit Sub
End If
Next
w.Activate
MsgBox (v & " not found")
End Sub


Click on the cell with the client number and run the macro above. The macro
goes to a sheet named "details" and looks in column C for that client number.
If the client number is found, that cell is selected. If the client number
is not found, a warning is issued.


You must substitute the proper tab name for "details" and the proper column
for column C.

 

[Barbara]

Hi Gary,
Thaks a lot. It worked just fine.
But one more little thing! How can I present my record in a Form.
I tried this: (had to define the variable, hope this is correct.)

Sub TakeMeThere()
Dim v
Dim w
Dim n
Dim i

v = ActiveCell.Value
Set w = ActiveSheet
Sheets("Contactos").Activate
n = Cells(Rows.Count, "B").End(xlUp).Row
For i = 1 To n
If Cells(i, "B").Value = v Then
Cells(i, "B").Select
ActiveSheet.ShowDataForm *********** (my new Line)
Exit Sub
End If
Next
w.Activate
MsgBox (v & " não existe !")
End Sub

The thing is that is shows the Form for the first record and not the one I
asked him to find! How can I fix this?
Hope to hear from you soon.
Thanks,
Barbara

 

[Gary''s Student]

My apologies...I know nothing about userforms...sorry.

 

[Barbara]

Nevertheless, thank you anyway. You helped me a lot.
Barbara

 

[Dave Peterson]

This assumes that your database used with data|form starts in A1 and the key
column is column A.

Option Explicit
Sub TakeMeThere2()

Dim myVal As Variant
Dim wks As Worksheet
Dim RngToSearch As Range
Dim FoundCell As Range

myVal = ActiveCell.Value
If Trim(myVal) = "" Then
'get out
Beep
Exit Sub
End If

Set wks = Worksheets("Contactos")
With wks
Set RngToSearch = .Range("a:a")
End With

With RngToSearch
Set FoundCell = .Cells.Find(what:=myVal, _
after:=.Cells(.Cells.Count), _
lookat:=xlWhole, _
LookIn:=xlValues, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)
End With

If FoundCell Is Nothing Then
Beep
MsgBox myVal & " wasn't found!"
Exit Sub
End If

SendKeys "{DOWN " & FoundCell.Row - 2 & "}"
Application.DisplayAlerts = False
wks.ShowDataForm
Application.DisplayAlerts = True

End Sub

 

[Barbara]

Thank you Dave, It worked just fine!
Barbara

This assumes that your database used with data|form starts in A1 and the key
column is column A.

Option Explicit
Sub TakeMeThere2()

Dim myVal As Variant
Dim wks As Worksheet
Dim RngToSearch As Range
Dim FoundCell As Range

myVal = ActiveCell.Value
If Trim(myVal) = "" Then
'get out
Beep
Exit Sub
End If

Set wks = Worksheets("Contactos")
With wks
Set RngToSearch = .Range("a:a")
End With

With RngToSearch
Set FoundCell = .Cells.Find(what:=myVal, _
after:=.Cells(.Cells.Count), _
lookat:=xlWhole, _
LookIn:=xlValues, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)
End With

If FoundCell Is Nothing Then
Beep
MsgBox myVal & " wasn't found!"
Exit Sub
End If

SendKeys "{DOWN " & FoundCell.Row - 2 & "}"
Application.DisplayAlerts = False
wks.ShowDataForm
Application.DisplayAlerts = True

End Sub