Need VBA Code

[Ewing25]

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!

 

[JLGWhiz]

"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]

It keeps giving me a compile error on the "With
activesheet.range(cells(2,A),(559,H))" Line. Any idea?

 

[JLGWhiz]

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]

Im really sorry but now its giving me a runtime error on the line
"lastRow = Cells(Rows.Count, sCol).End(xlUp).Row"

 

[JLGWhiz]

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]

Its saying "Runtime error 1004, Application-defined or Object-defined error"