C
Corey
The following code is part of a Userform to FIND Certain Values, provided there are Conditions Met.
The Data layout is:
A B C D E F G
1 FRED BLUE
2 615
3 25
4 31
5 87
6 7
7 60
8 75 <=== STRIKE THROUGH
9 20
10
11
12
13
14
15
16
17
18
19
20 JACK RED
21 614
22
etc.............
Therefore,
As i am LOOKING for a Numerical Value in Column C that,
1. Has a MATCHING value in Column C that is .Offset(-1, 2) from the ID Value in Column A that was
Selected in LISTBOX1, AND
2. Has a MATCHING value in Column G that is .Offset(-1, 6) from the ID Value in Column A that was
Selected in LISTBOX2, AND
3. The Value populated into LISTBOX3 is >= the Value Placed into TEXTBOX1, and
4. The Numerical Value populated into LISTBOX3 is NOT StrikeThrough.
Currently the BELOW code works GREAT, BUT it ONLY lists the Numerical Values in 1(ONE) Section of
DATA in Relation to the ID Value in Column A(615)
Is there a NEXT/LOOP Line or similar that will ADD ALL other Conditional Values that have the
Conditions Met?
There is About 200 Column A values, EACH with its Data in the Above Layout.
I need to include these Values also to populate in the Listbox3 too.
Private Sub TextBox1_Change()
Application.ScreenUpdating = False
ListBox3.Clear
If TextBox1.Value <> "" Then
Dim LastCell As Long
Dim myrow As Long
Dim i As Long
On Error Resume Next
LastCell = Worksheets("InspectionData").Cells(Rows.Count, "A").End(xlUp).Row
With ActiveWorkbook.Worksheets("InspectionData")
For myrow = 1 To LastCell
If .Cells(myrow, 1) <> "" And .Cells(myrow, 1).Offset(-1, 2).Value = ListBox1.Value And
_
.Cells(myrow, 1).Offset(-1, 6).Value = ListBox2.Value Then
For i = 2 To 22
If .Cells(myrow, 3).Offset(i, 0).Font.Strikethrough = False And Cells(myrow, 3).Offset(i,
0).Value <> "" _
And IsNumeric(.Cells(myrow, 3).Offset(i, 0)) Then
If .Cells(myrow, 3).Value >= TextBox1.Value Then
ListBox3.AddItem Cells(myrow, 3).Offset(i, 0)
ListBox3.List(ListBox3.ListCount - 1, 1) = Cells(myrow, 3).Offset(i, 0).Address
End If
End If
Next i
End If
Next
End With
End If
Application.ScreenUpdating = True
End Sub
Corey....
The Data layout is:
A B C D E F G
1 FRED BLUE
2 615
3 25
4 31
5 87
6 7
7 60
8 75 <=== STRIKE THROUGH
9 20
10
11
12
13
14
15
16
17
18
19
20 JACK RED
21 614
22
etc.............
Therefore,
As i am LOOKING for a Numerical Value in Column C that,
1. Has a MATCHING value in Column C that is .Offset(-1, 2) from the ID Value in Column A that was
Selected in LISTBOX1, AND
2. Has a MATCHING value in Column G that is .Offset(-1, 6) from the ID Value in Column A that was
Selected in LISTBOX2, AND
3. The Value populated into LISTBOX3 is >= the Value Placed into TEXTBOX1, and
4. The Numerical Value populated into LISTBOX3 is NOT StrikeThrough.
Currently the BELOW code works GREAT, BUT it ONLY lists the Numerical Values in 1(ONE) Section of
DATA in Relation to the ID Value in Column A(615)
Is there a NEXT/LOOP Line or similar that will ADD ALL other Conditional Values that have the
Conditions Met?
There is About 200 Column A values, EACH with its Data in the Above Layout.
I need to include these Values also to populate in the Listbox3 too.
Private Sub TextBox1_Change()
Application.ScreenUpdating = False
ListBox3.Clear
If TextBox1.Value <> "" Then
Dim LastCell As Long
Dim myrow As Long
Dim i As Long
On Error Resume Next
LastCell = Worksheets("InspectionData").Cells(Rows.Count, "A").End(xlUp).Row
With ActiveWorkbook.Worksheets("InspectionData")
For myrow = 1 To LastCell
If .Cells(myrow, 1) <> "" And .Cells(myrow, 1).Offset(-1, 2).Value = ListBox1.Value And
_
.Cells(myrow, 1).Offset(-1, 6).Value = ListBox2.Value Then
For i = 2 To 22
If .Cells(myrow, 3).Offset(i, 0).Font.Strikethrough = False And Cells(myrow, 3).Offset(i,
0).Value <> "" _
And IsNumeric(.Cells(myrow, 3).Offset(i, 0)) Then
If .Cells(myrow, 3).Value >= TextBox1.Value Then
ListBox3.AddItem Cells(myrow, 3).Offset(i, 0)
ListBox3.List(ListBox3.ListCount - 1, 1) = Cells(myrow, 3).Offset(i, 0).Address
End If
End If
Next i
End If
Next
End With
End If
Application.ScreenUpdating = True
End Sub
Corey....