Tricky summation query

[bat18]

I need a formula that will sum up to a blank cell. Is there such a forumla or macro that will do that??

Something along the lines of typing a formula into D30 which would mean the following

IF (A30<100, sum(C30 upwards to next blank cell), 0).

A macro may be required for this so any help would be great

Thanks

 

[Joe4]

This macro should work. Just place your cursor in the cell you want to put your sum in and run:

Sub MyFormulaPopulation()
 
    Dim myCurRow As Long
    Dim myPrevBlankRow As Long
    
    myCurRow = ActiveCell.Row
    myPrevBlankRow = Cells(myCurRow, "C").End(xlUp).Row
    Cells(myCurRow, "D").Formula = "=IF(A" & myCurRow & "<100,SUM(C" & myPrevBlankRow & ":C" & myCurRow & "),0)"
 
End Sub

 

[John Davis]

I need a formula that will sum up to a blank cell. Is there such a forumla or macro that will do that??

Something along the lines of typing a formula into D30 which would mean the following

IF (A30<100, sum(C30 upwards to next blank cell), 0).

A macro may be required for this so any help would be great

Thanks

Maybe:

Sub bat18()

If Range("A30").Value < 100 Then

    Range("C30").Select
    Do While ActiveCell <> ""
        ActiveCell.Offset(-1).Select
    Loop
       
   Range("D30").Value = WorksheetFunction.Sum(Range(ActiveCell, Range("C30")))
    
    

End If

 

[Joe4]

I don't think you want to hard code in row reference of 30 in your code, or else it will only work for row 30!

I also got the impression that the wanted the entire IF statement as part of the resulting formula which gets written to the cell, which is why I did it that way. Otherwise, it won't be dynamic (in case the value in column A ever changes).

 

[John Davis]

I don't think you want to hard code in row reference of 30 in your code, or else it will only work for row 30!

I also got the impression that the wanted the entire IF statement as part of the resulting formula which gets written to the cell, which is why I did it that way. Otherwise, it won't be dynamic (in case the value in column A ever changes).

You're right. I had been working on the thread and for what it's worth, I just posted it anyway. Yours is much better.

 

[bat18]

Cheers very much!!