Sum If Column

[marvellousmaz]

I write quite a bit of vba but I really cant get my head around this one!

I have entered this in Cell L204

=SUMIF(N204:Z204,">0")

Works a treat.

I would like a vba that enters this in All rows this a value in column N

Don't know why but I just cant seem to get my head around it today!

Any help would be appreciated.

Thanks

 

[marvellousmaz]

Just copy the formula down. The row references will automatically adjust to reflect the row they are in.

Yes that works fine. I would like to run a macro to do it though.

 

[Joe4]

Like this?

Sub MyMacro()


    Dim LastRow As Long


'   Find last row
    LastRow = ActiveCell.SpecialCells(xlLastCell).Row
    
'   Place formula in column AA for all rows
    Range("AA1:AA" & LastRow).FormulaR1C1 = _
        "=IF(LEN(RC[-13])>0,SUMIF(RC[-13]:RC[-1],"">0""),"""")"


End Sub

 

[marvellousmaz]

Thats great thanks.

I have been messing about with if's and loops and stuff for ages! You know when you just cant see a simple solution!

I settled on this in the end.

Sub Addhours()




Dim LastRow As Long


Sheets("sheet1").Select
ActiveSheet.Range("B2000").End(xlUp).Offset(1, 0).Select

LastRow = ActiveCell.Row

Range("L11:L" & LastRow).FormulaR1C1 = _
"=IF(LEN(RC[+2])>0,SUMIF(RC[+2]:RC[+20],"">0""),"""")"



End Sub

Works a treat

Thanks again