Excel Recalculation Frustration

[butler]

Greeting!

I have created a simple Excel VB function to do matching. See below for the code. Two cells are pass to the function and they are combined to do a lookup in two different tables and return a value. There are 2 comments in the code.

I am 'DEBUG STOP #1' code to do a break each time the key is "123190". This condition only occurs one time on the spread sheet but the code will break here 3 times?

'DEBUG STOP #2' is used to assign the value of cell DATA!I5 to a variable. There is a value of 770.46 in this cell, however the first two passes always return null.

Does anyone know why the code sometimes returns an incorrect value for the DATA!I5 cell? Also this cell is used thousands of times on the spreadsheet. A lot of time could be saved if it didn't do the same thing 3 times.

Thanks much,
--bill

Function doIS(Account, Division) As Double
Dim buff1 As String
Dim buff2 As String
Dim myKey As String
Dim myRange As Range
Dim i

buff1 = Trim(Str(Division))
Do While Len(buff1) < 2
buff1 = &quot;0&quot; & buff1
Loop

buff2 = Trim(Str(Account))
Do While Len(buff2) < 4
buff2 = &quot;0&quot; & buff2
Loop

myKey = buff1 & buff2
''' DEBUG SPOT #1
If myKey = &quot;123190&quot; Then
buff1 = &quot;&quot;
End If

''' DEBUG SPOT #2
buff1 = &quot;DATA!I5&quot;
buff2 = Range(buff1).Value
buff2 = Range(&quot;DATA!I5&quot.Value

''' LOOK FOR MANUAL OVERRIDE VALUES FIRST '''
Set myRange = Range(Range(&quot;ManualKeyRange&quot.Value)

On Error Resume Next
i = Application.WorksheetFunction.Match(myKey, myRange, 0)
If Err.Number <> 0 Then i = 0
On Error GoTo 0

If i > 1 Then
buff1 = Range(&quot;ManualValueRange&quot.Value & i
buff2 = Range(buff1).Value
Set myRange = Range(buff1)
'Set myRange = Range(Range(&quot;ManualValueRange&quot.Value & i)
doIS = myRange.Value
Else
''' LOOK FOR A MATCH IN THE ARRAY DATA '''
Set myRange = Range(Range(&quot;ArrayKeyRange&quot.Value)

On Error Resume Next
i = Application.WorksheetFunction.Match(myKey, myRange, 0)
If Err.Number <> 0 Then i = 0
On Error GoTo 0

If i > 1 Then
Set myRange = Range(Range(&quot;ArrayValueRange&quot.Value & i)
doIS = myRange.Value
Else
doIS = 0
End If
End If
End Function