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:
Sub AddTotalWork()
lastrow = Sheets("Daily Work").Range("C" & Rows.Count).End(xlUp).Offset(3).Row
lastrow2 = Sheets("Daily Work").Range("C" & Rows.Count).End(xlUp).Offset(4).Row
Sheets("Daily Work").Range("K" & lastrow).Value = "Total Hours Remaining"
Sheets("Daily Work").Range("K" & lastrow2).Value = "Minimum Required Manpower"
Range("B2:AB200").Interior.ColorIndex = xlNone
Call TotalHrs
Call TotalManpower
End Sub
Function TotalHrs()
FLastRow = Range("C" & Rows.Count).End(xlUp).Row
lastrow = Range("C" & Rows.Count).End(xlUp).Offset(3).Row
Sheets("Daily Work").Range("O" & lastrow).Formula = "=SUM(O2:O" & FLastRow & ")"
End Function
Function TotalManpower()
FFLastrow = Range("C" & Rows.Count).End(xlUp).Offset(4).Row
lastrow2 = Range("C" & Rows.Count).End(xlUp).Offset(4).Row
Sheets("Daily Work").Range("O" & FFLastrow).Formula = "=ROUNDUP(AVERAGE(AB2:AB" & lastrow2 & ",0),0)"
Call TotalDays
End Function
Function TotalDays()
'This is the function that give the #NAME error
Dim TotalHrs As Long
Dim Manpower As Long
Dim lastrow As Long
Dim lr As Long
TotalHrs = Range("O" & Rows.Count).End(xlUp).Offset(-1).Row
Manpower = Range("O" & Rows.Count).End(xlUp).Row
lastrow = Range("C" & Rows.Count).End(xlUp).Offset(5).Row
lr = Range("C" & Rows.Count).End(xlUp).Offset(5).Row
Sheets("Daily Work").Range("O" & lastrow).Formula = "=ROUND(TotalHrs/Manpower/8, 0)"
End Function
Everything works well except the last function. Any help would be greatly appreciated. Thanks!
Bookmarks