C
Corey ....
I am populating a listbox with conditions that need to be met.
However the conditions are not always met, but the value is being aded to
the listbox anyhow for some reason.
Basically the code needs to ensure that the value selected in Listbox5 and
Listbox6 are Offset from the numerical value in Column C.
Listbox5 value will also be in column C and Listbox6 value will be in Column
G.
I have added the conditions int he code more than once, to try to ensure
only the correct data is populated into the listbox 3, but i STILL get
values that Do NOT relate to the Listbox5 value.
Here is my code:
**********************************************
Private Sub CommandButton2_Click()
Application.ScreenUpdating = False
If TextBox111.Value <> "" Then
ListBox3.Clear
Dim LastCell As Long
Dim myrow As Long
LastCell = Worksheets("InspectionData").Cells(Rows.Count, "C").End(xlUp).Row
With ActiveWorkbook.Worksheets("InspectionData")
..Select
For myrow = 1 To LastCell
If .Cells(myrow, 3).Value = ListBox5.Value And .Cells(myrow,
3).Offset(0, 4).Value = ListBox6.Value Then ' <=====(1st instance) Condition
not 100% working
If .Cells(myrow, 3).Value = ListBox5.Value And
..Cells(myrow, 3).Offset(0, 4).Value = ListBox6.Value And _ ' ' <=====(2nd
instance) Condition not 100% working
.Cells(myrow, 3) <> "" And .Cells(myrow, 3).Value <> 0
And _
.Cells(myrow, 3).Value >= Val(TextBox111.Value) Then
For i = 3 To 23
If .Cells(myrow, 3).Value = ListBox5.Value And .Cells(myrow,
3).Offset(0, 4).Value = ListBox6.Value Then ' <=====(3rd instance) Condition
not 100% working
If ListBox5.Value = .Cells(myrow, 3).Value And ListBox6.Value =
..Cells(myrow, 3).Offset(0, 4).Value And _
IsNumeric(.Cells(myrow, 3).Offset(i, 0).Value) = True And
..Cells(myrow, 3).Offset(i, 0).Font.Strikethrough = False _
And .Cells(myrow, 3).Offset(i, 0).Value >= Val(TextBox111.Value)
Then
ListBox3.AddItem .Cells(myrow, 3).Offset(i, 0).Value
ListBox3.List(ListBox3.ListCount - 1, 1) = .Cells(myrow,
3).Offset(i, 1).Value
TextBox112.Value = ListBox3.ListCount
If .Cells(myrow, 3).Value <> ListBox5.Value Then ListBox3.Clear '
<=====(4th instance) Condition not 100% working
End If
End If
Next i
End If
End If
Next
End With
End If
If TextBox111.Value = "" Then
MsgBox "You did not Enter a Section Length to Find ! ", vbExclamation,
"Title ...."
TextBox111.Value = ""
TextBox111.SetFocus
End If
If ListBox3.ListCount < 1 Then
MsgBox "There are No Section Lengths Found (>=) for " & vbCrLf & vbTab &
Me.ListBox5.Value & vbCrLf & vbTab & Me.ListBox6.Value & vbCrLf & vbTab & "
being >= " & Me.TextBox111.Value & " !"
TextBox111.Value = ""
TextBox111.SetFocus
End If
Sheets("Main").Activate
Application.ScreenUpdating = True
End Sub
*********************************************************
Can anyone see why i do not get the correct list in listbox 3?
However the conditions are not always met, but the value is being aded to
the listbox anyhow for some reason.
Basically the code needs to ensure that the value selected in Listbox5 and
Listbox6 are Offset from the numerical value in Column C.
Listbox5 value will also be in column C and Listbox6 value will be in Column
G.
I have added the conditions int he code more than once, to try to ensure
only the correct data is populated into the listbox 3, but i STILL get
values that Do NOT relate to the Listbox5 value.
Here is my code:
**********************************************
Private Sub CommandButton2_Click()
Application.ScreenUpdating = False
If TextBox111.Value <> "" Then
ListBox3.Clear
Dim LastCell As Long
Dim myrow As Long
LastCell = Worksheets("InspectionData").Cells(Rows.Count, "C").End(xlUp).Row
With ActiveWorkbook.Worksheets("InspectionData")
..Select
For myrow = 1 To LastCell
If .Cells(myrow, 3).Value = ListBox5.Value And .Cells(myrow,
3).Offset(0, 4).Value = ListBox6.Value Then ' <=====(1st instance) Condition
not 100% working
If .Cells(myrow, 3).Value = ListBox5.Value And
..Cells(myrow, 3).Offset(0, 4).Value = ListBox6.Value And _ ' ' <=====(2nd
instance) Condition not 100% working
.Cells(myrow, 3) <> "" And .Cells(myrow, 3).Value <> 0
And _
.Cells(myrow, 3).Value >= Val(TextBox111.Value) Then
For i = 3 To 23
If .Cells(myrow, 3).Value = ListBox5.Value And .Cells(myrow,
3).Offset(0, 4).Value = ListBox6.Value Then ' <=====(3rd instance) Condition
not 100% working
If ListBox5.Value = .Cells(myrow, 3).Value And ListBox6.Value =
..Cells(myrow, 3).Offset(0, 4).Value And _
IsNumeric(.Cells(myrow, 3).Offset(i, 0).Value) = True And
..Cells(myrow, 3).Offset(i, 0).Font.Strikethrough = False _
And .Cells(myrow, 3).Offset(i, 0).Value >= Val(TextBox111.Value)
Then
ListBox3.AddItem .Cells(myrow, 3).Offset(i, 0).Value
ListBox3.List(ListBox3.ListCount - 1, 1) = .Cells(myrow,
3).Offset(i, 1).Value
TextBox112.Value = ListBox3.ListCount
If .Cells(myrow, 3).Value <> ListBox5.Value Then ListBox3.Clear '
<=====(4th instance) Condition not 100% working
End If
End If
Next i
End If
End If
Next
End With
End If
If TextBox111.Value = "" Then
MsgBox "You did not Enter a Section Length to Find ! ", vbExclamation,
"Title ...."
TextBox111.Value = ""
TextBox111.SetFocus
End If
If ListBox3.ListCount < 1 Then
MsgBox "There are No Section Lengths Found (>=) for " & vbCrLf & vbTab &
Me.ListBox5.Value & vbCrLf & vbTab & Me.ListBox6.Value & vbCrLf & vbTab & "
being >= " & Me.TextBox111.Value & " !"
TextBox111.Value = ""
TextBox111.SetFocus
End If
Sheets("Main").Activate
Application.ScreenUpdating = True
End Sub
*********************************************************
Can anyone see why i do not get the correct list in listbox 3?