'Divide the value of non-blank cells (in a column) amongst empty cells, delete original row and repeat with vba
I start with a sheet like this:
row1: 20
row2: (empty)
row3: (empty)
row4: 60
row5: (empty)
row6: 45
row7: (empty)
row8: (empty)
row9: 88
row10: (empty)
row11: 10
...
rowN: 67
I want to divide the value of non-empty cells ex 15 in row 1 into the number of following cells that are blank and then delete the original row so it would look like this after the transformation column O will look like this:
colB:
row2: 10
row3: 10
row5: 60
row7: 22.5
row8: 22.5
row10: 88
...
rowN: 67
There is a very similar question: Divide the value of non-blank cells (in a column) amongst empty cells and repeat with vba
but i can`t get rid of rows or the division by the blanks +1 in his case as i just want the division to be done by number of blank cells below.
Would appreciate any help with this.
I kinda got it to work like this:
Dim max As Long, i As Long, b As Long, cell As Range
Set cell = Range("K2")
max = Range("K" & Rows.Count).End(xlUp).Row
Do
i = i + 1
If (cell.Offset(i, 0).Value <> "") Then
b = i - 1
Range(cell, cell.Offset(i - 1, 0)).Value = cell.Value / b
Set cell = cell.Offset(i, 0)
i = 0
End If
If cell.Row = max Then Exit Sub
Loop
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
