Solved: Code to find a given string in a worksheet

[jmarkc]

Hoping someone can help. I've tried many different ways to accomplish this, but have had no success. Would greatly
appreciate it if someone can provide some insight...

I have a workbook that uses a userform to interact with the user and want it to ultimately sort data based on what
the user chooses. For the life of me, I can't seem to get the code right when I need to find their choice in a given
range in a given workbook. I think it's probably simple, but I just can't seem to get it right.

So, the userform asks the user to choose 1 of 3 options (A, B, or C). If the choice is A, the code will look in another
worksheet (Financial Info) in range H6:H800 to find a match. If there's a match, that row is copied and pasted to another worksheet (Output). There may be several matches and all matches will need to be copied and pasted.

I have added one of the the codes I have tried.

[vba]Private Sub OkayButton_Click()
Application.ScreenUpdating = False
Dim lItem As Long
Dim SpendRng As Range
Dim spName As String
Dim snName As String
Dim snFound As Range
Dim snRange1 As Range
Dim snRange2 As Range
Dim snRange3 As Range
Dim snFinance As Range

Sheets("output").Range("b5:l1000").Clear

' Enter chosen supplier name from combobox into "Output" sheet
For lItem = 0 To SupplierNameListBox.ListCount - 1
If SupplierNameListBox.Selected(lItem) = True Then
Sheets("output").Range("d1000").End(xlUp)(2, -1) = SupplierNameListBox.List(lItem)

' Find Supplier Name from 1 sheet in another sheet
snName = SupplierNameListBox.List(lItem)

On Error Resume Next
With Sheets("Data List")
Set snFound = .Columns(1).Find(What:=snName, After:=.Cells(1, 1), LookIn:=xlValues, lookat:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
On Error GoTo 0
If Not snFound Is Nothing Then Application.Goto snFound, True
End With
' Once Supplier Name is found, copy data & paste it
Set snRange1 = Selection.Offset(0, 3)
Set snRange2 = snRange1.End(xlDown)
Set snRange3 = snRange2.End(xlToRight)
Set snFinance = snRange1.Offset(0, -2)

On Error Resume Next
If snRange1 = "No Public Records" Then
Range(snRange1, snRange1).Copy Destination:=Sheets("Output").Range("b1000").End(xlUp).Offset(0, 2)
Sheets("Output").Range("b1000").End(xlUp).Offset(0, 1) = snFinance

Else
Range(snRange1, snRange3).Copy Destination:=Sheets("Output").Range("b1000").End(xlUp).Offset(0, 2)
Sheets("Output").Range("b1000").End(xlUp).Offset(0, 1) = snFinance
End If

SupplierNameListBox.Selected(lItem) = False
Sheets("output").Activate
End If
Next

'=========================================================
'------------------NEED HELP HERE!!-----------------------
'=========================================================

' Dim SpendClass0 As String
Dim scFound As Range
If SpendClassListBox.ListIndex = 0 Then
SpendClass0 = "A"

' Find Spend Class A in another sheet, copy & paste it

On Error Resume Next
With Sheets("Financial Info")
Set scFound = .Columns(1).Find(What:="A", After:=.Cells(1, 1), LookIn:=xlValues, lookat:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
On Error GoTo 0
If Not scFound Is Nothing Then Application.Goto scFound, True
ActiveCell.EntireRow.Copy Destination:=Sheets("Output").Range("a1000").End(xlUp).Offset(1, 0)
End With
Set snRange1 = Selection.Offset(0, 3)
Set snRange2 = snRange1.End(xlDown)
Set snRange3 = snRange2.End(xlToRight)
Set snFinance = snRange1.Offset(0, -2)

SupplierNameListBox.Selected(lItem) = False
Sheets("output").Activate

ElseIf SpendClassListBox.ListIndex = 1 Then
SpendClass1 = "B"
ElseIf SpendClassListBox.ListIndex = 2 Then
SpendClass2 = "C"

End If

Sheets("output").Columns("B").ColumnWidth = 32
Sheets("Output").Columns("C").HorizontalAlignment = xlCenter
Sheets("output").Columns("c").ColumnWidth = 20

Application.ScreenUpdating = True

Unload UserForm2

End Sub[/vba]

[lucas]

maybe filtering and copy:
[VBA]With Worksheets("sheet1")
'The 3 in the next line tells it to look in column C
.Cells.AutoFilter Field:=3, Criteria1:="=123", Operator:=xlOr, Criteria2:="=456"
.Range("A1", .Cells(.Rows.Count, 1).End(xlUp)).EntireRow.Copy
End With
Worksheets("errlist").Range("a1").PasteSpecial xlPasteAll[/VBA]

[jmarkc]

Thanks Lucas. That worked great. I have some cleanup to do, but it essentially gave me what I was looking for.

I have not coded the autofilter function before, so this is great!

[mdmackillop]

To clear the Autofilter, add this line at the end
[VBA]

Worksheets("sheet1").Cells.AutoFilter

[/VBA]

[jmarkc]

Thanks mdmackillop. I wasn't sure how to do that, but found this before you posted. It works well too:

[vba] Worksheets("Financial Info").AutoFilterMode = False
[/vba]

Appreciate the help. Like to know that I can rely on this site when I need the help...