find blank cells in column and average the cells below

[desibabuji]

Hello,

I have a worksheet that has some data that is a copy and paste from 2 different sheets. We calculate the percentage complete and then manually calculate the differences. I need some help in creating a macro to automate this process. Can someone please help.
Attached is a sample worksheet with "Sample" as the worksheet with the data and "Result" is what I am trying to achieve.

I want to be able to find the first blank cell in column E and then calculate the average of all the digits before the next blank cell is found and so on.
The rows between the blanks will vary based on the month and the week the report is run but will always have a blank cell before the data for the next person. The format will remain the same though.

Then, as per the results worksheet.
1. Insert 3 columns for Target % (E), Distribution % (F) and the Difference in % (G).
2. Calculate the average percentage for Distribution in Column (H).
3. Values in Column F are essentially copy of calculated values from column (H).
4. Column E is the % complete using the formula for Column (D) and (E).
5. And finally column G is the difference between column (E) and (F).

This is what I have so far
<code>

<code style="margin: 0px; padding: 0px; font-style: inherit;">Sub average_empty_cells()    Dim myAreas As Areas, myArea As Range    On Error Resume Next    Set myAreas = Range("e2", Range("e" & Rows.Count) _    .End(xlUp)).SpecialCells(2, 1).Areas    On Error GoTo 0    If myAreas Is Nothing Then Exit Sub    For Each myArea In myAreas        myArea(0).Formula = "=average(" & myArea.Address & ")"    Next    End Sub</code></pre>

</code>

Someone at the other forum suggested to use a formula instead and copy past the formula all the way in the row but I want to automate the process and not having to manually enter, paste and drag the formula everytime, as the number of rows can vary from 20 to upto 5000.

Here's a sample file https://www.box.com/s/fnyri3xdq863t75f3e8o

I really appreciate your help.

 

[jeffreybrown]

x-post

 

[desibabuji]

Thanks Jeff. I thought I had linked the words "other forum" as a link to my other post but it didnt work.

 

[mirabeau]

...
...

desibabuji,

For the first bit you mention, the averages in column E of your sample sheet, you can try this macro. I don't have time to consider the rest of your post, but it looks straightforward enough, and the macro should be the hardest part and should get you started

Sub getaverages()
Dim c As Range, av As Variant
Set c = Range("E" & Rows.Count).End(3)
Do
    If c(0) = "" Then
        av = "=" & c.Address
        Set c = c.End(3)
    Else
        av = "=average(" & Range(c, c.End(3)).Address & ")"
        Set c = c.End(3).End(3)
    End If
    c.End(4)(0) = av
Loop Until c.Row = 1
End Sub

If you've got lots of averages to calculate, you can speed it up a fair bit by putting in a couple of lines turning off automatic calculation at the start of the code and on again at the end. Not that it's particularly slow anyway.

 

[desibabuji]

Thanks mirabeau,

I will try to figure out the rest of it and if I cant solve it i will come and ask for help

Have a good rest of the evening and happy new year

 

[mirabeau]

Thanks mirabeau,

I will try to figure out the rest of it and if I cant solve it i will come and ask for help

Have a good rest of the evening and happy new year

as an alternative, somewhat shorter approach to the above, you could also look at

Sub avs()
Dim areaz As Areas, c As Range, q As Long
On Error Resume Next
Set areaz = Columns("E").SpecialCells(xlConstants).Areas
On Error GoTo 0
For Each c In areaz
    q = q + 1
    If q > 1 Then c(0) = "=average(" & c.Address & ")"
Next c
End Sub

 

[desibabuji]

Hi mirabeau,

Thanks a lot for this. Can I also ask you for a little modification, please ! If we have to find the weighted average as in column (f) of the sample below. Can you please modify the code?

https://www.box.com/s/tdk08ozd8nz1cn8y9bfh

Thanks a lot for your help, I really appreciate it.