C
Corey
Code below removes any duplicate values, but i want to place in Textbox8 the amount of duplicates(if
any) of the selected value.
How can i do this?
I tried:
Textbox8.value = Listbox1.ListCount :- Gives me how many items are listed
Textbox8.value = Listbox1.ListIndex :-Gives the Index position
I want:
Textbox8.value - Listbox1.Value (Instances)
Private Sub ListBox1_Change()
Application.ScreenUpdating = False
ListBox3.Clear
TextBox5.Value = ""
If ListBox2.ListCount > 0 Then ListBox2.Clear
Dim LastCell As Long
Dim myrow As Long
Dim NoDupes As Collection
On Error Resume Next
LastCell = Worksheets("Data").Cells(Rows.Count, "BH").End(xlUp).Row
With ActiveWorkbook.Worksheets("Data")
.Select
Set NoDupes = New Collection
For myrow = 1 To LastCell
If .Cells(myrow, 5).Value = ListBox1.Value Then
If .Cells(myrow, 60) <> "" Then
NoDupes.Add .Cells(myrow, 60).Value, CStr(.Cells(myrow, 60).Value)
If Err.Number = 0 Then
ListBox2.AddItem .Cells(myrow, 60)
Else
Err.Clear
End If
End If
End If
Next
End With
TextBox6.Value = ListBox2.ListCount
'TextBox8.Value = <============== Here
Application.ScreenUpdating = True
End Sub
any) of the selected value.
How can i do this?
I tried:
Textbox8.value = Listbox1.ListCount :- Gives me how many items are listed
Textbox8.value = Listbox1.ListIndex :-Gives the Index position
I want:
Textbox8.value - Listbox1.Value (Instances)
Private Sub ListBox1_Change()
Application.ScreenUpdating = False
ListBox3.Clear
TextBox5.Value = ""
If ListBox2.ListCount > 0 Then ListBox2.Clear
Dim LastCell As Long
Dim myrow As Long
Dim NoDupes As Collection
On Error Resume Next
LastCell = Worksheets("Data").Cells(Rows.Count, "BH").End(xlUp).Row
With ActiveWorkbook.Worksheets("Data")
.Select
Set NoDupes = New Collection
For myrow = 1 To LastCell
If .Cells(myrow, 5).Value = ListBox1.Value Then
If .Cells(myrow, 60) <> "" Then
NoDupes.Add .Cells(myrow, 60).Value, CStr(.Cells(myrow, 60).Value)
If Err.Number = 0 Then
ListBox2.AddItem .Cells(myrow, 60)
Else
Err.Clear
End If
End If
End If
Next
End With
TextBox6.Value = ListBox2.ListCount
'TextBox8.Value = <============== Here
Application.ScreenUpdating = True
End Sub