autosum and find percentage

[solidENM]

I am in need of some help with this one. I need a macro that will autosum column I, and then multiple by 5%. The tricky part is not every row has data. I am using this sheet copy/pasting data into a estimated price excel. Not every item has a predefined price, and comes up as N/A. When I use the regular autosum, it will only go from that cell up to the first n/a it finds, and i have to manually edit the formula to get to line 2. (row 1 is a header row).

in short: need code that will use the last cell in column i, and autosum up to line 2, and *.05.

the code i have so far finds the last open cell, enters ibf in column D, and "est freight" in column C. I need column I to be autosum of everything above, *5%.
here is where i need the code inserted.

lMaxRows = Cells(Rows.Count, "B").End(xlUp).Row
Range("D" & lMaxRows + 2).Select
ActiveCell = "IBF"
Range("c" & lMaxRows + 2).Select
ActiveCell = "Est Freight"
With Sheets("Quote Sheet")
With .Range("i" & .Rows.Count).End(xlUp)
.Offset(1).Formula = "=SUM(i1:i" & .Row & ")"

 

[Snakehips]

solidENM,

If you are using later than XL2007 then maybe use the Aggregate function ?
Set parameters for Sum and Ignore Errors

lMaxRows = Cells(Rows.Count, "B").End(xlUp).Row
Range("D" & lMaxRows + 2) = "IBF"
Range("c" & lMaxRows + 2) = "Est Freight"
With Sheets("Quote Sheet")
With .Range("i" & .Rows.Count).End(xlUp)
.Offset(1).Formula = "=AGGREGATE(9,6,I2:I" & .Row & ") * 1.05"
End With
End With

Hope that helps.

 

[solidENM]

thanks for the help, i havent heard of the aggregate function before. it is acting a little strange though. On the first sheet i tested it out on, it worked fine and also dynamically updated the prices as i made changes. i tried on two other sheets and on one it didnt populate at all, on the other it was a static number. You definately gave me something to look into, it seems like this may be the right track. thanks for the speedy reply!