I have around a hundred cells that do a sumproduct formula to calculate total footage of various sized pipe listed on a worksheet called 'Detail' (see the formula below. This works in around 90% of the situations because the total rows in the 'Detail' sheet is less than 6000, the fixed value in the formular. Rrecently we noticed that on larger jobs and/or different sort combinations we exceeded the 6000 fixed last row. I setup a function to dynamically calculate the last row of the 'Detail' sheet for each job but can't figure out how to replace the 6000 in the formula with the DetailLR() function. I've tried adding "" & and '' &, even placing the function in a cell and using that reference. I've tried using Value(Function), but everything has failed so far. I know that I can write the formula using VBA each time we load the worksheet, but I though there must be an easier way. Any ideas?
Thank you
Bob
Code:
=SUMPRODUCT((Detail!$E$10:$E$6000=Utilities!$H$9)*(Detail!$C$10:$C$6000=Utilities!J8)*(Detail!$B$10:$B$6000))+SUMPRODUCT((Detail!$E$10:$E$6000=Utilities!$H$9)*(Detail!$C$10:$C$6000=Utilities!J7)*(Detail!$B$10:$B$6000))
Code:
Public Function DetailLR() As Long
Application.Volatile (True)
Dim sh As Worksheet
Set sh = ActiveWorkbook.Worksheets("Detail")
On Error Resume Next
DetailLR = sh.Cells.Find(what:="*", _
After:=sh.Range("A1"), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function
Thank you
Bob