UDF Calculation Issue - Cells don't "hold" value

[jackdemo]

Hello,

Using Excel 2007 on Windows 7. I've created a function to calculate a total based on a number of factors. The function calculates the right answer and works fine within a given worksheet; however, when I pop between sheets, I receive a #Value error, and it requires a recalculation (F9) to refresh. I need the cell to "hold" the value.

Thanks in advance for your help! Here's the vba code.


Function GetTotalDollars(ar As Integer, ac As Integer, SheetName As String) ' As String
'Application.Volatile
'Debug.Print ar
'Debug.Print ac
'ActiveSheet.EnableCalculation = False
'ActiveSheet.EnableCalculation = True
Dim ColVal(1, 3)
Dim Rng1 As Range
Dim Rng2 As Range
Dim Rng3 As Range
Dim Rng4 As Range
Dim Rng5 As Range
Dim Rng6 As Range
Dim curCell1 As Variant
Dim curCell2 As Variant
Dim curCell3 As Variant
Dim DaysPerWeek As Integer
Dim HoursPerDay As Integer
Dim DaysPerMonth As Integer
Dim DollarsSpent As Double
Dim DollarsSpentSum As Double
Dim Proj As String
Dim ProjPLC As String
Dim ControlSheet As String
Dim iCount As Integer
Dim Max As Integer
'Dim ac As Integer
'Dim ar As Integer
ControlSheet = "forecast_control_sheets.xlsm"
Set Rng1 = ActiveWorkbook.Worksheets(SheetName).Range("A6:D25")
Set Rng2 = ActiveWorkbook.Worksheets(SheetName).Range("B6:D25")
Set Rng3 = ActiveWorkbook.Worksheets(SheetName).Range(Cells(6, ac), Cells(25, ac))
Set Rng4 = Workbooks(ControlSheet).Names("ProjectPLCKeyRange").RefersToRange
Set Rng5 = Workbooks(ControlSheet).Names("PLCRatesOY_DataRange").RefersToRange
Set Rng6 = Workbooks(ControlSheet).Names("BillRateYearHeaderRange").RefersToRange

DaysPerWeek = Sheets("forecast").Range("D26").Value
HoursPerDay = Sheets("forecast").Range("D27").Value
DollarsSpent = 0
DollarsSpentSum = 0
Proj = Sheets(SheetName).Range("$A$1").Value
Max = Sheets(SheetName).Range("A6:D25").Rows.Count - 1
'ReDim ColVal(1 To Max)
'Debug.Print ("row= " & ar)
'Debug.Print ("column= " & ac)

For iCount = 1 To Max
DaysPerMonth = Sheets(SheetName).Cells(5, ac)

Set curCell1 = Rng1(iCount, 1)
Set curCell2 = Rng2(iCount, 1)
Set curCell3 = Rng3(iCount, 1)

ColVal(1, 1) = curCell1
ColVal(1, 2) = curCell2
ColVal(1, 3) = curCell3

ProjPLC = Proj & "-" & ColVal(1, 2)
'Debug.Print (ProjPLC)
'Debug.Print ColVal(1, 1)
'Debug.Print ColVal(1, 2)
'Debug.Print ColVal(1, 3)

If ColVal(1, 3) > 0 Then
'Debug.Print ColVal(1, 3)
'Debug.Print (Cells(2, ac).Value)

MatchVal1 = WorksheetFunction.Match(ProjPLC, Rng4, 0)
MatchVal2 = WorksheetFunction.Match(Sheets(SheetName).Cells(2, ac), Rng6, 0)
IndexVal1 = WorksheetFunction.Index(Rng5, MatchVal1, MatchVal2)

If SheetName = "forecast" Then
DollarsSpent = IndexVal1 * HoursPerDay * DaysPerMonth * ColVal(1, 3)
ElseIf SheetName = "Burn" Then
DollarsSpent = IndexVal1 * ColVal(1, 3)
End If

DollarsSpentSum = DollarsSpentSum + DollarsSpent

'Debug.Print (MatchVal1)
'Debug.Print (MatchVal2)
'Debug.Print (IndexVal1)
'Debug.Print (DollarsSpent)
'Debug.Print (DollarsSpentSum)
'Debug.Print (iCount)

End If

Next iCount

Debug.Print (DollarsSpentSum)
GetTotalDollars = DollarsSpentSum

Erase ColVal()

End Function