Excel VBA - Using CountA to total non-blank cells and calculate percentages

[Becky24]

I have a spreadsheet with headers where I am totaling columns E through I and column M and then I want to count the non-blank cells in column A and use that result to add percentages below each of the totaled columns. The number of rows could change from time to time so I need the spreadsheet to find the cells under each of the totals to enter the percentages. I’m doing a number of other things with my spreadsheet, but this part is really causing me a problem. I’ve tried a number of things, without success. I’m new to creating code in VBA and appreciate any assistance.

 

[RayFrye]

This might be a place to start:
Count Blank/Nonblank Cells in Excel

If this doesn't help, show us a scaled down picture of your problem.

 

[JLGWhiz]

Give this a try

Sub t()
Dim i As Long, a As Long, b As Double
With ActiveSheet
    For i = 5 To 13
        a = Application.CountA(.Cells(2, i).Resize(.Cells(Rows.Count, i).End(xlUp).Row - 2))
        b = .Cells(Rows.Count, i).End(xlUp).Value
        .Cells(Rows.Count, i).End(xlUp)(2) = b / a
    Next
End With
End Sub

 

[Becky24]

Thank you JLGWhiz, but that didn't seem to quite give me what I was looking for. Let me give a better picture of what I'm looking for. Below is a snapshot of a portion of my spreadsheet where I've run my VBA code up to the point of needing to calculate percentages. (The columns are different from my original question, but I still want to use Column A to get my count of non-blank cells and then calculate the percentages on line 69, but realize that it wouldn't always be line 69 as I want to allow for the number of rows to change.

 

[Becky24]

This might be a place to start:
Count Blank/Nonblank Cells in Excel

If this doesn't help, show us a scaled down picture of your problem.

Thank you. I am familiar with the formulas in Excel, but it doesn't really solve my problem for writing the VBA code I need.

 

[JLGWhiz]

Post #4 indicates that there will be cells with zero, these would be counted in the CountA function. Also, not mentioned in the OP is that the data is in a table, which could affect how the code reacts, since tables use use different syntax for rows and column references in some cases. The code I posted should find the last entry in the column and then post a value in the cell beneath that found cell. I am not sure what kind of percentage you are after, but perhaps swithching the 'b / a' in the code to 'a / b' would give you the percentage you are looking for.

 

[Becky24]

Post #4 indicates that there will be cells with zero, these would be counted in the CountA function. Also, not mentioned in the OP is that the data is in a table, which could affect how the code reacts, since tables use use different syntax for rows and column references in some cases. The code I posted should find the last entry in the column and then post a value in the cell beneath that found cell. I am not sure what kind of percentage you are after, but perhaps swithching the 'b / a' in the code to 'a / b' would give you the percentage you are looking for.

Thank you so much. Not sure if I didn't copy something correctly before or what, but this works great (see below). I revised for the columns I need it for. How do I change it to also perform the same routine for Column J(10), I don't want the other columns . Again, I'm new so appreciate all the help you've given.

Sub t()

Dim i As Long, a As Long, b As Double
With ActiveSheet
For i = 2 To 6
a = Application.CountA(.Cells(2, i).Resize(.Cells(Rows.Count, i).End(xlUp).Row - 2))
b = .Cells(Rows.Count, i).End(xlUp).Value
.Cells(Rows.Count, i).End(xlUp)(2) = b / a
Next
End With



End Sub

 

[JLGWhiz]

The For i = 2 To 6 covers columns B thru F. Since you have an interruption of two columns, you can just handle column J as a separate item after the For...Next loop. Insert the following line of code.

.Cells(Rows.Count, 10).End(xlUp)(2) = Application.CountA(.Cells(2, 10).Resize(.Cells(Rows.Count, 10).End(xlUp).Row - 2)) _
/ Cells(Rows.Count, 10).End(xlUp).Value

 

[JLGWhiz]

then I want to count the non-blank cells in column A

I went back and read the OP and realized that I have been counting the wrong column. This is a revised version

Sub t3()
Dim i As Long, a As Long, b As Double, fn As Range
    With ActiveSheet
        Set fn = .Columns(1).Find("# of Emp", , xlValues, xlPart)
        If Not fn Is Nothing Then
            a = Application.CountA(.Range("A2", fn.Offset(-1)))
        Else
            MsgBox "Cannot locate range marker!", vbCritical, "WORKSHEET ERROR"
            Exit Sub
        End If
        For i = 2 To 6
            b = .Cells(Rows.Count, i).End(xlUp).Value
            .Cells(Rows.Count, i).End(xlUp)(2) = a / b
        Next
        .Cells(Rows.Count, 10).End(xlUp)(2) = a / Cells(Rows.Count, 10).End(xlUp).Value
    End With
End Sub

 

[Becky24]

I went back and read the OP and realized that I have been counting the wrong column. This is a revised version

Sub t3()
Dim i As Long, a As Long, b As Double, fn As Range
    With ActiveSheet
        Set fn = .Columns(1).Find("# of Emp", , xlValues, xlPart)
        If Not fn Is Nothing Then
            a = Application.CountA(.Range("A2", fn.Offset(-1)))
        Else
            MsgBox "Cannot locate range marker!", vbCritical, "WORKSHEET ERROR"
            Exit Sub
        End If
        For i = 2 To 6
            b = .Cells(Rows.Count, i).End(xlUp).Value
            .Cells(Rows.Count, i).End(xlUp)(2) = a / b
        Next
        .Cells(Rows.Count, 10).End(xlUp)(2) = a / Cells(Rows.Count, 10).End(xlUp).Value
    End With
End Sub

That is perfectly fine. I did realize what you were doing which will work just fine as all the columns I'm totalling and creating percentages for will have something in all the cells(no blank cells). The 0 means they haven't taken a course and the 1 means they have, so I'm totalling the 1's and then dividing by the total participants (row count) to get my percentages. Thank you for above though. I'll study that as it might be useful to me in the future.