Your code is inconsistent. You have
For n = 0 To Me.ListBox3.ListCount - 1
which makes n the loop index that will be 0, 1, 2 etc., but then you have
If Me.ListBox3.Selected(i) Then
which uses a variable i that doesn't have anything to do with the loop index n.
Moreover, in the lines
Code: Select all
If Me.ListBox3.Selected(i) Then
strMet = Me.ListBox3.List(i)
End If
ws.Cells(r, 7).Value = strMet
you use strMet regardless of whether the list item was selected or not, because ws.Cells(r, 7).Value = strMet comes AFTER the End If. And it is not clear what r is here - you have used it in a loop higher up in the thread but that loop has already ended.
You have to make your code consistent and logical.
Something that will help is indenting the code consistently. It is currently difficult to follow because the indenting is haphazard.
For example, if you have a line If ... Then, the corresponding End If should have the same left indent, and the lines in between If ... Then and End If should be indented further.
If you format your code
Code: Select all
For i = 0 To Me.List_sites2.ListCount - 1
If Me.List_sites2.Selected(i) Then
strSel = Me.List_sites2.List(i)
For r = 1 To iMaxRow
If ws.Cells(r, 4).Value = strSel And ws.Cells(r, 5).Value = datDate Then
Exit For
End If
Next r
If r > iMaxRow Then
iRow = iRow + 1
r = iRow
End If
ws.Cells(r, 4).Value = strSel
ws.Cells(r, 5).Value = datDate
End If
Next i
like this:
Code: Select all
For i = 0 To Me.List_sites2.ListCount - 1
If Me.List_sites2.Selected(i) Then
strSel = Me.List_sites2.List(i)
For r = 1 To iMaxRow
If ws.Cells(r, 4).Value = strSel And ws.Cells(r, 5).Value = datDate Then
Exit For
End If
Next r
If r > iMaxRow Then
iRow = iRow + 1
r = iRow
End If
ws.Cells(r, 4).Value = strSel
ws.Cells(r, 5).Value = datDate
End If
Next i
it becomes somewhat easier to follow because you can see at a glance what belongs to what.
