Hello

Below is all the info i use. For security i can't share the entire workbook.

I would like to find a way to grab the raw data when the ticker is searched and output all the rows with that ticker and the correct data beside it. as you can see rtd has the info coming from a vlookup which just looks up the first item....




VBAkrazy.xlsx


Private Sub OptionButton1_Click()
Sheets("PrePreDrive").Range("M2").Value = 1
End Sub

Private Sub OptionButton2_Click()
Sheets("PrePreDrive").Range("M2").Value = 2
End Sub

Private Sub OptionButton3_Click()
Sheets("PrePreDrive").Range("M2").Value = 3
End Sub

Private Sub OptionButton4_Click()
Sheets("PrePreDrive").Range("M2").Value = 4
End Sub

Private Sub OptionButton5_Click()
Sheets("PrePreDrive").Range("M2").Value = 5
End Sub

Private Sub OptionButtonSelector1_Click()
Sheets("Spin1").Range("D4").Value = 1
End Sub

Private Sub OptionButtonSelector2_Click()
Sheets("Spin1").Range("D4").Value = 2
End Sub

Private Sub OptionButtonSelector3_Click()
Sheets("Spin1").Range("D4").Value = 3
End Sub

Private Sub OptionButtonTicker1_Click()
Sheets("PrePreDrive").Range("O2").Value = 1
End Sub

Private Sub OptionButtonTicker2_Click()
Sheets("PrePreDrive").Range("O2").Value = 2
End Sub

Private Sub OptionButtonTicker3_Click()
Sheets("PrePreDrive").Range("O2").Value = 3
End Sub

Private Sub OptionButtonTicker4_Click()
Sheets("PrePreDrive").Range("O2").Value = 4
End Sub

Private Sub OptionButtonTicker5_Click()
Sheets("PrePreDrive").Range("O2").Value = 5
End Sub

Private Sub OptionButtonTickerB1_Click()
Sheets("PrePreDrive").Range("P2").Value = 1
End Sub

Private Sub OptionButtonTickerB2_Click()
Sheets("PrePreDrive").Range("P2").Value = 2
End Sub

Private Sub OptionButtonTickerB3_Click()
Sheets("PrePreDrive").Range("P2").Value = 3
End Sub

Private Sub OptionButtonTickerB4_Click()
Sheets("PrePreDrive").Range("P2").Value = 4
End Sub

Private Sub OptionButtonTickerB5_Click()
Sheets("PrePreDrive").Range("P2").Value = 5
End Sub

Private Sub SpinButton1_Change()
Sheets("Spin1").Range("A1").Value = SpinButton1.Value
End Sub

Private Sub SpinButton2_Change()
Sheets("Spin1").Range("A2").Value = SpinButton2.Value
End Sub

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Ticker = TextBox1.Text
Sheets("Spin1").Range("A4").Value = Ticker
End Sub


Private Sub CommandButton1_Click()
TextBox1.Text = UCase(TextBox1.Text)
' Dim YourVariable As Variant
' Dim rowCount As Integer
' Set YourVariable = TextBox1
' Sheets("Calc1").Activate
' With ActiveSheet.Range("B2:B200")
' Set uRng = .FIND(YourVariable, , xlValues, xlWhole, , MatchCase:=False, searchformat:=False)
' If Not uRng Is Nothing Then
' uRng.Activate
' rowCount = ActiveCell.Row
' End If
' ActiveCell.Columns("A:K").Copy
' Sheets("Dashboard1").Activate
' Range("D6").PasteSpecial Paste:=xlPasteValues
' End With
Dim YourTicker As Variant
Dim Det As Variant
Set YourTicker = TextBox1
On Error Resume Next
Det = Application.WorksheetFunction.VLookup(YourTicker, Sheets("Calc1").Range("E2:t400"), 1, False)
Range("D25") = Det
On Error Resume Next
Det = Application.WorksheetFunction.VLookup(YourTicker, Sheets("Calc1").Range("E2:t400"), 2, False)
Range("E25") = Det
On Error Resume Next
Det = Application.WorksheetFunction.VLookup(YourTicker, Sheets("Calc1").Range("E2:t400"), 3, False)
Range("F25") = Det
On Error Resume Next
Det = Application.WorksheetFunction.VLookup(YourTicker, Sheets("Calc1").Range("E2:t400"), 4, False)
Range("G25") = Det
On Error Resume Next
Det = Application.WorksheetFunction.VLookup(YourTicker, Sheets("Calc1").Range("E2:t400"), 5, False)
Range("H25") = Det
On Error Resume Next
Det = Application.WorksheetFunction.VLookup(YourTicker, Sheets("Calc1").Range("E2:t400"), 6, False)
Range("I25") = Det
On Error Resume Next
Det = Application.WorksheetFunction.VLookup(YourTicker, Sheets("Calc1").Range("E2:t400"), 7, False)
Range("J25") = Det
On Error Resume Next
Det = Application.WorksheetFunction.VLookup(YourTicker, Sheets("Calc1").Range("E2:t400"), 8, False)
Range("K25") = Det
On Error Resume Next
Det = Application.WorksheetFunction.VLookup(YourTicker, Sheets("Calc1").Range("E2:t400"), 9, False)
Range("L25") = Det
On Error Resume Next
Det = Application.WorksheetFunction.VLookup(YourTicker, Sheets("Calc1").Range("E2:t400"), 10, False)
Range("M25") = Det
If Err.Number <> 0 Then
''error appeared
MsgBox "Ticker not found" ''optional, no need to do anything
End If

On Error GoTo 0 ''no error, coming back to default conditions
TextBox1.Activate
TextBox1.Text = ""
End Sub

Private Sub TextBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Dim Ctrl As Control
If KeyCode = 13 Then
TextBox1.Text = UCase(TextBox1.Text)
' Dim YourVariable As Variant
' Dim rowCount As Integer
' Set YourVariable = TextBox1
' Sheets("Calc1").Activate
' With ActiveSheet.Range("B2:B200")
' Set uRng = .FIND(YourVariable, , xlValues, xlWhole, , MatchCase:=False, searchformat:=False)
' If Not uRng Is Nothing Then
' uRng.Activate
' rowCount = ActiveCell.Row
' End If
' ActiveCell.Columns("A:K").Copy
' Sheets("Dashboard1").Activate
' Range("D6").PasteSpecial Paste:=xlPasteValues
' End With
Dim YourTicker As Variant
Dim Det As Variant
Set YourTicker = TextBox1
On Error Resume Next
Det = Application.WorksheetFunction.VLookup(YourTicker, Sheets("Calc1").Range("E2:t400"), 1, False)
Range("D25") = Det
On Error Resume Next
Det = Application.WorksheetFunction.VLookup(YourTicker, Sheets("Calc1").Range("E2:t400"), 2, False)
Range("E25") = Det
On Error Resume Next
Det = Application.WorksheetFunction.VLookup(YourTicker, Sheets("Calc1").Range("E2:t400"), 3, False)
Range("F25") = Det
On Error Resume Next
Det = Application.WorksheetFunction.VLookup(YourTicker, Sheets("Calc1").Range("E2:t400"), 4, False)
Range("G25") = Det
On Error Resume Next
Det = Application.WorksheetFunction.VLookup(YourTicker, Sheets("Calc1").Range("E2:t400"), 5, False)
Range("H25") = Det
On Error Resume Next
Det = Application.WorksheetFunction.VLookup(YourTicker, Sheets("Calc1").Range("E2:t400"), 6, False)
Range("I25") = Det
On Error Resume Next
Det = Application.WorksheetFunction.VLookup(YourTicker, Sheets("Calc1").Range("E2:t400"), 7, False)
Range("J25") = Det
On Error Resume Next
Det = Application.WorksheetFunction.VLookup(YourTicker, Sheets("Calc1").Range("E2:t400"), 8, False)
Range("K25") = Det
On Error Resume Next
Det = Application.WorksheetFunction.VLookup(YourTicker, Sheets("Calc1").Range("E2:t400"), 9, False)
Range("L25") = Det
On Error Resume Next
Det = Application.WorksheetFunction.VLookup(YourTicker, Sheets("Calc1").Range("E2:t400"), 10, False)
Range("M25") = Det
If Err.Number <> 0 Then
''error appeared
MsgBox "Ticker not found" ''optional, no need to do anything
End If

On Error GoTo 0 ''no error, coming back to default conditions
TextBox1.Activate
TextBox1.Text = ""

End If
End Sub