本文发表在 rolia.net 枫下论坛In sheet 3, put;
H1: product name you wish
J1: color code you wish
L1: quality level you wish
Then run the following code.
****************************************8
Sub PickupCorrectProduct()
Dim ProductName, ColorCode, QualityLevel As String
Dim TotalLines As Integer
Dim i As Integer
Dim LineNoSheet1 As Integer
Dim LinesFound As Integer
LineNoSheet1 = 2
LinesFound = 0
ProductName = Trim(Sheet3.Range("H1")) 'get the desired product name
ColorCode = Trim(Sheet3.Range("J1")) ' get the desired color code
QualityLevel = Trim(Sheet3.Range("L1")) ' get the desired quality level
TotalLines = Sheet3.Range("A2").CurrentRegion.Rows.Count 'get the total lines
If ProductName = "" Then
MsgBox "Void product name!"
Beep
Exit Sub
ElseIf ColorCode = "" Then
MsgBox "Void color code!"
Beep
Exit Sub
ElseIf ProductName = "" Then
MsgBox "Void quality level!"
Beep
Exit Sub
End If
For i = 2 To TotalLines
If Sheet3.Cells(i, 1) = ProductName And Sheet3.Cells(i, 2) = ColorCode And Sheet3.Cells(i, 4) = QualityLevel Then
Sheet1.Cells(i, 1) = Sheet3.Cells(i, 1)
Sheet1.Cells(i, 2) = Sheet3.Cells(i, 2)
Sheet1.Cells(i, 3) = Sheet3.Cells(i, 3)
Sheet1.Cells(i, 4) = Sheet3.Cells(i, 4)
LineNoSheet1 = LineNoSheet1 + 1
LinesFound = LinesFound + 1
End If
Next i
Sheet1.Cells(LineNoSheet1, 1) = "Product:"
Sheet1.Cells(LineNoSheet1, 2) = ProductName
Sheet1.Cells(LineNoSheet1, 3) = "Color:"
Sheet1.Cells(LineNoSheet1, 4) = ColorCode
Sheet1.Cells(LineNoSheet1, 5) = "Quality:"
Sheet1.Cells(LineNoSheet1, 6) = QualityLevel
Sheet1.Cells(LineNoSheet1, 7) = "Lines Found:"
Sheet1.Cells(LineNoSheet1, 8) = LinesFound
End Sub更多精彩文章及讨论,请光临枫下论坛 rolia.net
H1: product name you wish
J1: color code you wish
L1: quality level you wish
Then run the following code.
****************************************8
Sub PickupCorrectProduct()
Dim ProductName, ColorCode, QualityLevel As String
Dim TotalLines As Integer
Dim i As Integer
Dim LineNoSheet1 As Integer
Dim LinesFound As Integer
LineNoSheet1 = 2
LinesFound = 0
ProductName = Trim(Sheet3.Range("H1")) 'get the desired product name
ColorCode = Trim(Sheet3.Range("J1")) ' get the desired color code
QualityLevel = Trim(Sheet3.Range("L1")) ' get the desired quality level
TotalLines = Sheet3.Range("A2").CurrentRegion.Rows.Count 'get the total lines
If ProductName = "" Then
MsgBox "Void product name!"
Beep
Exit Sub
ElseIf ColorCode = "" Then
MsgBox "Void color code!"
Beep
Exit Sub
ElseIf ProductName = "" Then
MsgBox "Void quality level!"
Beep
Exit Sub
End If
For i = 2 To TotalLines
If Sheet3.Cells(i, 1) = ProductName And Sheet3.Cells(i, 2) = ColorCode And Sheet3.Cells(i, 4) = QualityLevel Then
Sheet1.Cells(i, 1) = Sheet3.Cells(i, 1)
Sheet1.Cells(i, 2) = Sheet3.Cells(i, 2)
Sheet1.Cells(i, 3) = Sheet3.Cells(i, 3)
Sheet1.Cells(i, 4) = Sheet3.Cells(i, 4)
LineNoSheet1 = LineNoSheet1 + 1
LinesFound = LinesFound + 1
End If
Next i
Sheet1.Cells(LineNoSheet1, 1) = "Product:"
Sheet1.Cells(LineNoSheet1, 2) = ProductName
Sheet1.Cells(LineNoSheet1, 3) = "Color:"
Sheet1.Cells(LineNoSheet1, 4) = ColorCode
Sheet1.Cells(LineNoSheet1, 5) = "Quality:"
Sheet1.Cells(LineNoSheet1, 6) = QualityLevel
Sheet1.Cells(LineNoSheet1, 7) = "Lines Found:"
Sheet1.Cells(LineNoSheet1, 8) = LinesFound
End Sub更多精彩文章及讨论,请光临枫下论坛 rolia.net