Add additional Formula using two VBA Formulas

[dondada82]

I'm trying to create another function formula in excel vba by using to other formula I've created. The two other formulas are in the last row on top of each other(pretty much offset by 1). Below that I would like to have a other formula which takes the value of those two cells and divides them and divides again by 8. A normal formula in a cell would look like =ROUND(O2/O3/8,0). Because my last two rows with the value is constantly changing(i.e.O30 and O31, then 13 and O14, etc...) I was think I can use the find the two last rows and offset them by 1 the use the formula to get the value that I need, but I'm getting a #NAME error. Here's my formulas below:

Please Login or Register  to view this content.

Everything works well except the last function. Any help would be greatly appreciated. Thanks!

[MarvinP]

Hi don,

I think this line won't work:
Sheets("Daily Work").Range("O" & lastrow).Formula = "=ROUND(TotalHrs/Manpower/8, 0)"

try
Sheets("Daily Work").Range("O" & lastrow).Formula = "=ROUND(" & TotalHrs/Manpower/8 & ", 0)"

[dondada82]

MarvinP....Brilliant! Thank you!!

[dondada82]

Actually Marvin....its not working like it should. The excel formula bar shows:

=ROUND(0.119565217391304, 0)

The info I currently am dividing is 36.20/2/8 = 2.26 which should round to 2.3, but I'm getting 0. Would you know why that is?

[dondada82]

I think what the formul ais doing is taking the row count (i.e 23 (totalhrs) and 24(manpower)) then dividing it by 8, which leaves a value of zero. How can I identify the two cells as having 36.20 in O22 and 2 in O23 but making the row dynamic?

[dondada82]

Ok MarvinP.....I figured it out. This is what the formula now reads:

Sheets("Daily Work").Range("O" & lastrow).Formula = "=ROUND(" & Range("O" & TotalHrs) / Range("O" & Manpower) / 8 & ", 0)"

Thanks for your help.