VBA Loop & Count

[Noz2k]

Want I am trying to do is create a summary, where I want to count all the instances a name appears between a certain date range, where the value is "Yes" in another column.

The problem I am having is that this will be sent to more than 1 user group and the names will be different. So I want to use a combobox to determine the names.

here is a bit of my code so far

Sub Summary()
 
Dim rng                 As Range, _
    rng1                As String, _
    ws1                 As Worksheet, _
    ws2                 As Worksheet, _
    Count               As Long, _
    lngLastRow          As Long, _
    DF                  As String, _
    DT                  As String, _
    findoffset         As Long, _
    X                   As String
 
 
DF = Format(CDate(Sheets("MainMenu").txtFrom.Value), "dd/mm/yyyy")
DT = Format(CDate(Sheets("MainMenu").txtTo.Value), "dd/mm/yyyy")
lngLastRow = Sheets("Data").Cells(Rows.Count, "M").End(xlUp).Row
X = 0
Set ws1 = Sheets("Data")
Set ws2 = Sheets("Summary")
Count = 0
With ws1.Range("M3:M" & lngLastRow)
Set rng = .Find("Yes", LookIn:=xlValues)
    If Not rng Is Nothing Then
        rng1 = rng.Address
 
            Do
 
            If Format(CDate(rng.Offset(, -8)), "dd/mm/yyyy") >= DF And Format(CDate(rng.Offset(, -8)), "dd/mm/yyyy") < DT Then
                If rng.Offset(, 11) = Sheets("MainMenu").cboName.Value Then
                        Count = Count + 1
                End If
            End If
 
 
                Set rng = .FindNext(rng)
        Loop While Not rng Is Nothing And rng.Address <> rng1
    End If
End With
 
    ws2.Range("F5").Value = Count
 
End Sub

This is set up fine to return the count for 1 specific name. However I need to return a count for each name in the list cboName, and offset the range posted to by (1,0) each time.

Thinking I might need another loop somewhere, and I thought I'd use listindex (which is why I have Dim X As String in my code), but can't figure it out

 

[Noz2k]

Ok so I've now tried this, but still can't get it working. Any help on where I'm going wrong would be greatly appreciated. If I need to explain what I'm trying to do better then please let me know, thanks

Sub Summary()
 
Dim rng As Range, _
rng1 As String, _
ws1 As Worksheet, _
ws2 As Worksheet, _
Count As Long, _
lngLastRow As Long, _
DF As String, _
DT As String, _
X As Long, _
findoffset As Long
 
 
 
DF = Format(CDate(Sheets("MainMenu").txtFrom.Value), "dd/mm/yyyy")
DT = Format(CDate(Sheets("MainMenu").txtTo.Value), "dd/mm/yyyy")
lngLastRow = Sheets("Data").Cells(Rows.Count, "M").End(xlUp).Row
findoffset = 0
Set ws1 = Sheets("Data")
Set ws2 = Sheets("Summary")
With ws1.Range("M3:M" & lngLastRow)
For X = 0 To X = Sheets("MainMenu").cboname.ListCount - 1
Count = 0
Set rng = .Find("Yes", LookIn:=xlValues)
If Not rng Is Nothing Then
rng1 = rng.Address
 
Do
 
If Format(CDate(rng.Offset(, -8)), "dd/mm/yyyy") >= DF And Format(CDate(rng.Offset(, -8)), "dd/mm/yyyy") < DT Then
If rng.Offset(, 11) = Sheets("MainMenu").cboname.List(X) Then
Count = Count + 1
End If
End If
 
 
Set rng = .FindNext(rng)
Loop While Not rng Is Nothing And rng.Address <> rng1
End If
ws2.Range("F5").Offset(findoffset, 0) = Count
findoffset = findoffset + 1
Next X
End With
 
End Sub

 

[tweedle]

I think you're trying to loop through cbobox items....:
For X = 0 To Sheets("MainMenu").cboname.ListCount - 1

 

[Noz2k]

Yes, that is what I am trying to do.

I don't seem to be able to get it working though

Have changed my code to

Count = 0
X = 0
Do Until X > Sheets("MainMenu").cboname.ListCount - 1
With ws1.Range("M3:M" & lngLastRow)
Set rng = .Find("Yes", LookIn:=xlValues)
    If Not rng Is Nothing Then
        rng1 = rng.Address
 
            Do
 
            If Format(CDate(rng.Offset(, -8)), "dd/mm/yyyy") >= DF And Format(CDate(rng.Offset(, -8)), "dd/mm/yyyy") < DT Then
                If rng.Offset(, 11) = Sheets("MainMenu").cboname.List(X) Then
                        Count = Count + 1
                End If
            End If
 
 
                Set rng = .FindNext(rng)
        Loop While Not rng Is Nothing And rng.Address <> rng1
    End If
End With
ws2.Range("F5").Offset(findoffset, 0) = Count
findoffset = findoffset + 1
X = X + 1
Loop

But now although the findoffset value is correct, and it is posting for the correct amount of values in the combobox. The count values aren't right. Can you see where this is going wrong?

 

[tweedle]

Difficult to tell from my perspective.

Should this be <= DT?

If Format(CDate(rng.Offset(, -8)), "dd/mm/yyyy") >= DF And Format(CDate(rng.Offset(, -8)), "dd/mm/yyyy") < DT Then

 

[Noz2k]

Yeah, the date and that is fine.

The only problem is that the count is adding cumatively. Where I want it seperate for each name, it is adding.

At the moment it is posting like this:

2
3
5
5
7
etc

where it should be

2
1
2
0
2
etc

 

[tweedle]

Move Count = 0 to just below your Do Until X ...

 

[Noz2k]

Yeah, I just realised that one. Moving things about so much I didn't realise that was outside the loop.

Thanks for your help