Macro to insert sum calculation at every second blank cell for data above in column F
 

Hi

As the subject says

My data is like

Column A Column F
John 100.00
10.00
Total 110.00
Blank Row
Smith 100.00
10.00
Total 110.00
Blank Row


I have two blank rows between every part of my data,

I need to insert a sum formula to sum all the above to the previous
sum formula.

I already tried the edit go to special cells and used the sum button
but it inserted the formula in every blank cell for the column making
my data wrong,

I hope someone can help and I am being clear,

Thanks so much

Andrea

Macro to insert sum calculation at every second blank cell for data above in column F
 

On Jul 11, 1:40 pm, "
wrote:
Hi

As the subject says

My data is like

Column A Column F
John 100.00
10.00
Total 110.00
Blank Row
Smith 100.00
10.00
Total 110.00
Blank Row

I have two blank rows between every part of my data,

I need to insert a sum formula to sum all the above to the previous
sum formula.

I already tried the edit go to special cells and used the sum button
but it inserted the formula in every blank cell for the column making
my data wrong,

I hope someone can help and I am being clear,

There are many ways to do what you want. Here is a fairly simple way
that may help you see what you need to do.

Hope this helps

Peter


dim currRow as long
dim blankCount as integer
dim totalStr as string

totalStr="="
blankCount=0
currRow=1
While blankCount < 4
if (isempty(cells(currRow,6))) then
blankCount=blankCount+1
else
blankCount=0
if (cells(currRow,1)="Total") then
totalStr=totalStr+"+F"+format(currRow)
end if
end if
currRow=currRow+1
wend

cells(currRow,1)="Grand Total"
cells(currRow,6)=totalStr