jmarkc
06-08-2009, 01:39 PM
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.
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
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.
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