Aaron Greene
asked on
Unable to get the Index Property of the WorksheetFunction Class
I am using the following code to retrieve a value from a shifting range of cells. It worked fine on my laptop, but when I ran it on another machine, I started to get the error that states that Excel is "Unable to get the Index Property of the WorksheetFunction Class". My laptop is running Excel 2013 and the production pc is running Excel 2007. I'm stumped.
Public Function udf_CTRequired(TempC As Double, ph As Double, Resid As Double) As Double
Dim wb As Workbook
Dim ws As Worksheet
Dim rngTable As Range
Dim TempF As Double
Dim x As Long
Dim y As Long
Dim dblResult As Variant
Set wb = ThisWorkbook
Set ws = wb.Sheets("CTValues")
TempF = (9 / 5) * TempC + 32
'Determine starting cell according to Temperature
If TempF < 40.91 Then
Set rngTable = ws.Range("C3", "I16")
rngTable.Font.Bold = False
Else
If TempF >= 40.91 And TempF < 49.91 Then
Set rngTable = ws.Range("C17", "I30")
Else
If TempF >= 49.91 And TempF < 58.91 Then
Set rngTable = ws.Range("C31", "I44")
Else
If TempF >= 58.91 And TempF < 67.91 Then
Set rngTable = ws.Range("C45", "I58")
Else
If TempF >= 67.91 And TempF < 76.91 Then
Set rngTable = ws.Range("C59", "I72")
Else
If TempF >= 76.91 Then
Set rngTable = ws.Range("C73", "I86")
End If
End If
End If
End If
End If
End If
'Determine column offset according to pH
If ph < 6.04 Then
x = 0
Else
If ph >= 6.04 And ph < 6.55 Then
x = 1
Else
If ph >= 6.55 And ph < 7.04 Then
x = 2
Else
If ph >= 7.04 And ph < 7.55 Then
x = 3
Else
If ph >= 7.55 And ph < 8.04 Then
x = 4
Else
If ph >= 8.04 And ph < 8.55 Then
x = 5
Else
If ph >= 8.55 Then
x = 6
End If
End If
End If
End If
End If
End If
End If
'Determine row offset according to chlorine residual
If Resid < 0.5 Then
y = 0
Else
If Resid >= 0.5 And Resid < 0.7 Then
y = 1
Else
If Resid >= 0.7 And Resid < 0.9 Then
y = 2
Else
If Resid >= 0.9 And Resid < 1.1 Then
y = 3
Else
If Resid >= 1.1 And Resid < 1.3 Then
y = 4
Else
If Resid >= 1.3 And Resid < 1.5 Then
y = 5
Else
If Resid >= 1.5 And Resid < 1.7 Then
y = 6
Else
If Resid >= 1.7 And Resid < 1.9 Then
y = 7
Else
If Resid >= 1.9 And Resid < 2.1 Then
y = 8
Else
If Resid >= 2.1 And Resid < 2.3 Then
y = 9
Else
If Resid >= 2.3 And Resid < 2.5 Then
y = 10
Else
If Resid >= 2.5 And Resid < 2.7 Then
y = 11
Else
If Resid >= 2.7 And Resid < 2.9 Then
y = 12
Else
If Resid >= 2.9 Then
y = 13
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
dblResult = Application.WorksheetFunct ion.Index( rngTable, x, y)
udf_CTRequired = dblResult
End Function
Public Function udf_CTRequired(TempC As Double, ph As Double, Resid As Double) As Double
Dim wb As Workbook
Dim ws As Worksheet
Dim rngTable As Range
Dim TempF As Double
Dim x As Long
Dim y As Long
Dim dblResult As Variant
Set wb = ThisWorkbook
Set ws = wb.Sheets("CTValues")
TempF = (9 / 5) * TempC + 32
'Determine starting cell according to Temperature
If TempF < 40.91 Then
Set rngTable = ws.Range("C3", "I16")
rngTable.Font.Bold = False
Else
If TempF >= 40.91 And TempF < 49.91 Then
Set rngTable = ws.Range("C17", "I30")
Else
If TempF >= 49.91 And TempF < 58.91 Then
Set rngTable = ws.Range("C31", "I44")
Else
If TempF >= 58.91 And TempF < 67.91 Then
Set rngTable = ws.Range("C45", "I58")
Else
If TempF >= 67.91 And TempF < 76.91 Then
Set rngTable = ws.Range("C59", "I72")
Else
If TempF >= 76.91 Then
Set rngTable = ws.Range("C73", "I86")
End If
End If
End If
End If
End If
End If
'Determine column offset according to pH
If ph < 6.04 Then
x = 0
Else
If ph >= 6.04 And ph < 6.55 Then
x = 1
Else
If ph >= 6.55 And ph < 7.04 Then
x = 2
Else
If ph >= 7.04 And ph < 7.55 Then
x = 3
Else
If ph >= 7.55 And ph < 8.04 Then
x = 4
Else
If ph >= 8.04 And ph < 8.55 Then
x = 5
Else
If ph >= 8.55 Then
x = 6
End If
End If
End If
End If
End If
End If
End If
'Determine row offset according to chlorine residual
If Resid < 0.5 Then
y = 0
Else
If Resid >= 0.5 And Resid < 0.7 Then
y = 1
Else
If Resid >= 0.7 And Resid < 0.9 Then
y = 2
Else
If Resid >= 0.9 And Resid < 1.1 Then
y = 3
Else
If Resid >= 1.1 And Resid < 1.3 Then
y = 4
Else
If Resid >= 1.3 And Resid < 1.5 Then
y = 5
Else
If Resid >= 1.5 And Resid < 1.7 Then
y = 6
Else
If Resid >= 1.7 And Resid < 1.9 Then
y = 7
Else
If Resid >= 1.9 And Resid < 2.1 Then
y = 8
Else
If Resid >= 2.1 And Resid < 2.3 Then
y = 9
Else
If Resid >= 2.3 And Resid < 2.5 Then
y = 10
Else
If Resid >= 2.5 And Resid < 2.7 Then
y = 11
Else
If Resid >= 2.7 And Resid < 2.9 Then
y = 12
Else
If Resid >= 2.9 Then
y = 13
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
dblResult = Application.WorksheetFunct
udf_CTRequired = dblResult
End Function
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Can you describe the specific error? I tested this on a sample workbook with similarly-named sheets and a sample lookup table and had no issues.
ASKER
I had the x and y reversed. Everything is working fine. Reading the entire post would have saved me some time. Thanks.
ASKER