VBA Count Error

[Cowichandave]

In the result below countA is counting the entire column and showing 7 . It should only show 6 . The first number in column 5 (2701.12) is the sum and should not be counted. What am I forgetting? Sheet starts in Column C

VBA Code

Dim ws As Worksheet
    Dim rngFull As Range
    Dim arr As Variant
    Dim rowLast As Long, i As Long
    Dim empRowFirst As Long, empRowLast As Long
   
    Set ws = ActiveSheet
    With ws
        rowLast = .Cells(Rows.count, "E").End(xlUp).Row + 2 ' Section includes 2 empty rows
        Set rngFull = .Range("C1:S" & rowLast)
        arr = rngFull.Value
   
        For i = 1 To UBound(arr)
            If arr(i, 3) <> "" Then
                If arr(i - 1, 3) = "" Then
                    empRowFirst = i
                ElseIf arr(i + 1, 3) = "" Or i = (UBound(arr) - 1) Then
                    empRowLast = i
                 
                    .Range("G" & i + 1 & ":S" & i + 1).Formula = _
                        "=COUNT(" & .Range("G" & empRowFirst & ":G" & empRowLast).Address(1, 0) & ")"

RESULT

2			BON07	2701.12​
	1	CRE06		439.00​
	2	CAP21
	3	CAR21		215.47​
	4	CHE11
	5	CYC08		130.00​
	6	ELM01		416.66​
	7	FOOT07		770.00​
	8	ALL07
	9	ALL10
	10	MOR03
	11	NOR37
	12	PP07
	13	RCM180
	14	RCMPSER		729.99​
	15	ROG06
	16	UNI40
				7

 

[Michael M]

Should this be

For i =2 To UBound(arr)

 

[Cowichandave]

Only works for the first group. I have 50 groups. After the first this is the result for group 17. Here it shows 2 and should be 1

17			KimL				343.00​
	1	AMA08
	2	BES11
	3	MUNIWB					343.00​
				0	0	0	2

 

[Michael M]

If it happens for each group, then

"=COUNT(" & .Range("G" & empRowFirst & ":G" & empRowLast).Address(1, 0) & ")-1"

 

[Cowichandave]

If it happens for each group, then

"=COUNT(" & .Range("G" & empRowFirst & ":G" & empRowLast).Address(1, 0) & ")-1"

Very Interesting. The original code I had Group 1 CountA was correct. Groups 2 to 50 had countA 1 to many. With your adding the -1 groups 2 to 50 countA are correct but group 1 countA is short 1
Also blank colums are now -1

17			KimL				343.00​
	1	AMA08
	2	BES11
	3	MUNIWB					343.00​
				-1	-1	-1	1

 

[Michael M]

did yo change this back to 1 ??

For i =2 To UBound(arr)

 

[Cowichandave]

did yo change this back to 1 ??

For i =2 To UBound(arr)

No still at 2. Changing it back to 1 makes group 1 CountA 1 short. Group 2 to 50 are correct except if there is a blank column then CountA is -1

 

[Peter_SSs]

@Cowichandave
When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

It would also help if you posted

• your whole code so that we could test/modify it
• some small sample data including say three small groups, with existing formulas with XL2BB and explain in words in relation to that sample data exactly what you are trying to do

 

[Alex Blakenburg]

How are you getting your data ? I suspect that the cell in Column E that looks empty is not actually empty.

If you can rely on column D having the numeric values in it as in your sample data, you could give this a go.

Sub cntsections_V02()
    Dim ws As Worksheet
    Dim rngFull As Range
    Dim arr As Variant
    Dim rowLast As Long, i As Long
    Dim empRowFirst As Long, empRowLast As Long
   
    Set ws = ActiveSheet
    With ws
        rowLast = .Cells(Rows.Count, "E").End(xlUp).Row + 2 ' Section includes 2 empty rows
        Set rngFull = .Range("C1:S" & rowLast)
        arr = rngFull.Value
   
        For i = 2 To UBound(arr)
            If Val(arr(i, 2)) <> 0 Then
                If Val(arr(i - 1, 2)) = 0 Then
                    empRowFirst = i
                ElseIf Val(arr(i + 1, 2)) = 0 Or i = (UBound(arr) - 1) Then
                    empRowLast = i
                 
                    .Range("G" & i + 1 & ":S" & i + 1).Formula = _
                        "=COUNT(" & .Range("G" & empRowFirst & ":G" & empRowLast).Address(1, 0) & ")"
                End If
            End If
        Next i
    End With
End Sub