Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Searching data within sheets
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Searching data within sheets
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. -- Gary''s Student - gsnu200767 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Searching data within sheets
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" wrote: 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. -- Gary''s Student - gsnu200767 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Searching data within sheets
My apologies...I know nothing about userforms...sorry.
-- Gary''s Student - gsnu200767 "Barbara" wrote: 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" wrote: 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. -- Gary''s Student - gsnu200767 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Searching data within sheets
Nevertheless, thank you anyway. You helped me a lot.
Barbara "Gary''s Student" wrote: My apologies...I know nothing about userforms...sorry. -- Gary''s Student - gsnu200767 "Barbara" wrote: 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" wrote: 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. -- Gary''s Student - gsnu200767 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Searching data within sheets
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 wrote: 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" wrote: 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. -- Gary''s Student - gsnu200767 -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Searching data within sheets
Thank you Dave, It worked just fine!
Barbara "Dave Peterson" wrote: 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 wrote: 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" wrote: 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. -- Gary''s Student - gsnu200767 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
searching all sheets in a wb | Excel Worksheet Functions | |||
searching all workbook sheets | Excel Worksheet Functions | |||
Data searching between sheets | Excel Discussion (Misc queries) | |||
Data searching between sheets | New Users to Excel | |||
Find (Ctrl +F) not searching all open sheets | Excel Worksheet Functions |