PDA

View Full Version : [SOLVED:] Filling Empty Cells with Data based on Averages



Sully1440
02-04-2019, 10:17 AM
Hi All,
I'm looking for some help adding numbers to a very large data set. I have numbers in column C intermittently. I want to do the following:
1.) If a number is in Column C, count the number of empty cells below it (but only up to the next number)...ie. C17,....C27 are empty cells, so the count would be 11
2.) Divide this count (i.e. 11) by the number above the empty cells (i.e C16 = 9; therefore 9/11 = 0.818
3.) Fill the empty cells below it with this average but only the empty cells (i.e. 0.818)
4.) Continue with the next number to the end of the data set

That's it. Sample data attached.

Any help would be appreciated.
Jim

Paul_Hossler
02-04-2019, 11:36 AM
Something like this




Option Explicit

Sub Numbers()

Dim rData As Range, rArea As Range, rAbove As Range
Set rData = ActiveSheet.Cells(1, 1).CurrentRegion.Columns(3).SpecialCells(xlCellTypeBlanks)

For Each rArea In rData.Areas
With rArea
Set rAbove = .Cells(1, 1).End(xlUp)
rArea.Value = rAbove.Value / rArea.Cells.Count
End With
Next

End Sub

Sully1440
02-04-2019, 11:56 AM
Works Perfectly. Thank you Paul :)