Need VBA Code
Hey guys,
Im not sure how to do this at all. What i wanted to do is make it so when you click on a certain worksheet tab a text box comes up that asks you a question and when you put in a number from a certain column it will come up with a query of the information in the same row as that number you put in. Also if it doesnt find your number it comes up with the entire worksheet. Can this be done? Ive tried doing research on macros and VBA code but i cant seem to find anything relating to that type of project. Thanks! |
Need VBA Code
"Comes up with" is not in the VBA language reference, so I improvised.
Copy this code to the Sheet code module for the worksheet that you want to execute the search on. Right click the sheet tab, then click on View Code in the drop down menu. Private Sub Worksheet_Activate() Dim c As Range, pickCol As String, itemToFind as Variant, _ lastRow As Long pickCol = InputBox("Enter a letter for a column to query", _ "Select Column") itemToFind =InputBox("Enter the data to be found", "Search Criteria") lastRow = Cells(RowsCount, pickCol).End(xlUP).Row With ActiveSheet.Range(Cells(2,pickCol), (lastRow, pickCol)) Set c = .Find(itemToFind, LookIn:=xlValues) If Not c Is Nothing Then ActiveWindow.ScrollRow = c.Row ActiveWindow.ScrollColumn = 1 Else ActiveWindow.ScrollRow = 1 ActiveWindow.Scroll.Column = 1 End If End Sub "Ewing25" wrote: Hey guys, Im not sure how to do this at all. What i wanted to do is make it so when you click on a certain worksheet tab a text box comes up that asks you a question and when you put in a number from a certain column it will come up with a query of the information in the same row as that number you put in. Also if it doesnt find your number it comes up with the entire worksheet. Can this be done? Ive tried doing research on macros and VBA code but i cant seem to find anything relating to that type of project. Thanks! |
Need VBA Code
It keeps giving me a compile error on the "With
activesheet.range(cells(2,A),(559,H))" Line. Any idea? "JLGWhiz" wrote: "Comes up with" is not in the VBA language reference, so I improvised. Copy this code to the Sheet code module for the worksheet that you want to execute the search on. Right click the sheet tab, then click on View Code in the drop down menu. Private Sub Worksheet_Activate() Dim c As Range, pickCol As String, itemToFind as Variant, _ lastRow As Long pickCol = InputBox("Enter a letter for a column to query", _ "Select Column") itemToFind =InputBox("Enter the data to be found", "Search Criteria") lastRow = Cells(RowsCount, pickCol).End(xlUP).Row With ActiveSheet.Range(Cells(2,pickCol), (lastRow, pickCol)) Set c = .Find(itemToFind, LookIn:=xlValues) If Not c Is Nothing Then ActiveWindow.ScrollRow = c.Row ActiveWindow.ScrollColumn = 1 Else ActiveWindow.ScrollRow = 1 ActiveWindow.Scroll.Column = 1 End If End Sub "Ewing25" wrote: Hey guys, Im not sure how to do this at all. What i wanted to do is make it so when you click on a certain worksheet tab a text box comes up that asks you a question and when you put in a number from a certain column it will come up with a query of the information in the same row as that number you put in. Also if it doesnt find your number it comes up with the entire worksheet. Can this be done? Ive tried doing research on macros and VBA code but i cant seem to find anything relating to that type of project. Thanks! |
Need VBA Code
That happens when I don't test them before posting. This one is tested and
should work as intended. Delete the other one and paste this one in its place. Private Sub Worksheet_Activate() Dim c As Range, pickCol As String, itemToFind As Variant, _ lastRow As Long pickCol = InputBox("Enter a letter for a column to query", _ "Select Column") sCol = UCase(pickCol) itemToFind = InputBox("Enter the data to be found", "Search Criteria") lastRow = Cells(Rows.Count, sCol).End(xlUp).Row With ActiveSheet.Range(Cells(2, sCol), Cells(lastRow, sCol)) Set c = .Find(itemToFind, LookIn:=xlValues) If Not c Is Nothing Then ActiveWindow.ScrollRow = c.Row ActiveWindow.ScrollColumn = 1 Else ActiveWindow.ScrollRow = 1 ActiveWindow.Scroll.Column = 1 End If End With End Sub "Ewing25" wrote: It keeps giving me a compile error on the "With activesheet.range(cells(2,A),(559,H))" Line. Any idea? "JLGWhiz" wrote: "Comes up with" is not in the VBA language reference, so I improvised. Copy this code to the Sheet code module for the worksheet that you want to execute the search on. Right click the sheet tab, then click on View Code in the drop down menu. Private Sub Worksheet_Activate() Dim c As Range, pickCol As String, itemToFind as Variant, _ lastRow As Long pickCol = InputBox("Enter a letter for a column to query", _ "Select Column") itemToFind =InputBox("Enter the data to be found", "Search Criteria") lastRow = Cells(RowsCount, pickCol).End(xlUP).Row With ActiveSheet.Range(Cells(2,pickCol), (lastRow, pickCol)) Set c = .Find(itemToFind, LookIn:=xlValues) If Not c Is Nothing Then ActiveWindow.ScrollRow = c.Row ActiveWindow.ScrollColumn = 1 Else ActiveWindow.ScrollRow = 1 ActiveWindow.Scroll.Column = 1 End If End Sub "Ewing25" wrote: Hey guys, Im not sure how to do this at all. What i wanted to do is make it so when you click on a certain worksheet tab a text box comes up that asks you a question and when you put in a number from a certain column it will come up with a query of the information in the same row as that number you put in. Also if it doesnt find your number it comes up with the entire worksheet. Can this be done? Ive tried doing research on macros and VBA code but i cant seem to find anything relating to that type of project. Thanks! |
Need VBA Code
Im really sorry but now its giving me a runtime error on the line
"lastRow = Cells(Rows.Count, sCol).End(xlUp).Row" "JLGWhiz" wrote: That happens when I don't test them before posting. This one is tested and should work as intended. Delete the other one and paste this one in its place. Private Sub Worksheet_Activate() Dim c As Range, pickCol As String, itemToFind As Variant, _ lastRow As Long pickCol = InputBox("Enter a letter for a column to query", _ "Select Column") sCol = UCase(pickCol) itemToFind = InputBox("Enter the data to be found", "Search Criteria") lastRow = Cells(Rows.Count, sCol).End(xlUp).Row With ActiveSheet.Range(Cells(2, sCol), Cells(lastRow, sCol)) Set c = .Find(itemToFind, LookIn:=xlValues) If Not c Is Nothing Then ActiveWindow.ScrollRow = c.Row ActiveWindow.ScrollColumn = 1 Else ActiveWindow.ScrollRow = 1 ActiveWindow.Scroll.Column = 1 End If End With End Sub "Ewing25" wrote: It keeps giving me a compile error on the "With activesheet.range(cells(2,A),(559,H))" Line. Any idea? "JLGWhiz" wrote: "Comes up with" is not in the VBA language reference, so I improvised. Copy this code to the Sheet code module for the worksheet that you want to execute the search on. Right click the sheet tab, then click on View Code in the drop down menu. Private Sub Worksheet_Activate() Dim c As Range, pickCol As String, itemToFind as Variant, _ lastRow As Long pickCol = InputBox("Enter a letter for a column to query", _ "Select Column") itemToFind =InputBox("Enter the data to be found", "Search Criteria") lastRow = Cells(RowsCount, pickCol).End(xlUP).Row With ActiveSheet.Range(Cells(2,pickCol), (lastRow, pickCol)) Set c = .Find(itemToFind, LookIn:=xlValues) If Not c Is Nothing Then ActiveWindow.ScrollRow = c.Row ActiveWindow.ScrollColumn = 1 Else ActiveWindow.ScrollRow = 1 ActiveWindow.Scroll.Column = 1 End If End Sub "Ewing25" wrote: Hey guys, Im not sure how to do this at all. What i wanted to do is make it so when you click on a certain worksheet tab a text box comes up that asks you a question and when you put in a number from a certain column it will come up with a query of the information in the same row as that number you put in. Also if it doesnt find your number it comes up with the entire worksheet. Can this be done? Ive tried doing research on macros and VBA code but i cant seem to find anything relating to that type of project. Thanks! |
Need VBA Code
And what does the message say? I am not getting one, so it is difficult to
determine what the problem might be without knowing what the message says. "Ewing25" wrote: Im really sorry but now its giving me a runtime error on the line "lastRow = Cells(Rows.Count, sCol).End(xlUp).Row" "JLGWhiz" wrote: That happens when I don't test them before posting. This one is tested and should work as intended. Delete the other one and paste this one in its place. Private Sub Worksheet_Activate() Dim c As Range, pickCol As String, itemToFind As Variant, _ lastRow As Long pickCol = InputBox("Enter a letter for a column to query", _ "Select Column") sCol = UCase(pickCol) itemToFind = InputBox("Enter the data to be found", "Search Criteria") lastRow = Cells(Rows.Count, sCol).End(xlUp).Row With ActiveSheet.Range(Cells(2, sCol), Cells(lastRow, sCol)) Set c = .Find(itemToFind, LookIn:=xlValues) If Not c Is Nothing Then ActiveWindow.ScrollRow = c.Row ActiveWindow.ScrollColumn = 1 Else ActiveWindow.ScrollRow = 1 ActiveWindow.Scroll.Column = 1 End If End With End Sub "Ewing25" wrote: It keeps giving me a compile error on the "With activesheet.range(cells(2,A),(559,H))" Line. Any idea? "JLGWhiz" wrote: "Comes up with" is not in the VBA language reference, so I improvised. Copy this code to the Sheet code module for the worksheet that you want to execute the search on. Right click the sheet tab, then click on View Code in the drop down menu. Private Sub Worksheet_Activate() Dim c As Range, pickCol As String, itemToFind as Variant, _ lastRow As Long pickCol = InputBox("Enter a letter for a column to query", _ "Select Column") itemToFind =InputBox("Enter the data to be found", "Search Criteria") lastRow = Cells(RowsCount, pickCol).End(xlUP).Row With ActiveSheet.Range(Cells(2,pickCol), (lastRow, pickCol)) Set c = .Find(itemToFind, LookIn:=xlValues) If Not c Is Nothing Then ActiveWindow.ScrollRow = c.Row ActiveWindow.ScrollColumn = 1 Else ActiveWindow.ScrollRow = 1 ActiveWindow.Scroll.Column = 1 End If End Sub "Ewing25" wrote: Hey guys, Im not sure how to do this at all. What i wanted to do is make it so when you click on a certain worksheet tab a text box comes up that asks you a question and when you put in a number from a certain column it will come up with a query of the information in the same row as that number you put in. Also if it doesnt find your number it comes up with the entire worksheet. Can this be done? Ive tried doing research on macros and VBA code but i cant seem to find anything relating to that type of project. Thanks! |
Need VBA Code
Its saying "Runtime error 1004, Application-defined or Object-defined error"
"JLGWhiz" wrote: And what does the message say? I am not getting one, so it is difficult to determine what the problem might be without knowing what the message says. "Ewing25" wrote: Im really sorry but now its giving me a runtime error on the line "lastRow = Cells(Rows.Count, sCol).End(xlUp).Row" "JLGWhiz" wrote: That happens when I don't test them before posting. This one is tested and should work as intended. Delete the other one and paste this one in its place. Private Sub Worksheet_Activate() Dim c As Range, pickCol As String, itemToFind As Variant, _ lastRow As Long pickCol = InputBox("Enter a letter for a column to query", _ "Select Column") sCol = UCase(pickCol) itemToFind = InputBox("Enter the data to be found", "Search Criteria") lastRow = Cells(Rows.Count, sCol).End(xlUp).Row With ActiveSheet.Range(Cells(2, sCol), Cells(lastRow, sCol)) Set c = .Find(itemToFind, LookIn:=xlValues) If Not c Is Nothing Then ActiveWindow.ScrollRow = c.Row ActiveWindow.ScrollColumn = 1 Else ActiveWindow.ScrollRow = 1 ActiveWindow.Scroll.Column = 1 End If End With End Sub "Ewing25" wrote: It keeps giving me a compile error on the "With activesheet.range(cells(2,A),(559,H))" Line. Any idea? "JLGWhiz" wrote: "Comes up with" is not in the VBA language reference, so I improvised. Copy this code to the Sheet code module for the worksheet that you want to execute the search on. Right click the sheet tab, then click on View Code in the drop down menu. Private Sub Worksheet_Activate() Dim c As Range, pickCol As String, itemToFind as Variant, _ lastRow As Long pickCol = InputBox("Enter a letter for a column to query", _ "Select Column") itemToFind =InputBox("Enter the data to be found", "Search Criteria") lastRow = Cells(RowsCount, pickCol).End(xlUP).Row With ActiveSheet.Range(Cells(2,pickCol), (lastRow, pickCol)) Set c = .Find(itemToFind, LookIn:=xlValues) If Not c Is Nothing Then ActiveWindow.ScrollRow = c.Row ActiveWindow.ScrollColumn = 1 Else ActiveWindow.ScrollRow = 1 ActiveWindow.Scroll.Column = 1 End If End Sub "Ewing25" wrote: Hey guys, Im not sure how to do this at all. What i wanted to do is make it so when you click on a certain worksheet tab a text box comes up that asks you a question and when you put in a number from a certain column it will come up with a query of the information in the same row as that number you put in. Also if it doesnt find your number it comes up with the entire worksheet. Can this be done? Ive tried doing research on macros and VBA code but i cant seem to find anything relating to that type of project. Thanks! |
All times are GMT +1. The time now is 07:28 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com