The problem is in the line AvgPts = 0
Since I have a custom function AvgPts() in the workbook, It appears that
AvgPts is a reserve word. Changing this statement to Ghin = 0, which is the
variable in the new routine, the procedure works.
Thanks for all your help. Where do you live in England?
charlie

"Charles Woll" wrote in message
...
I have created worksheet function =avgpts() It gathers in scores that
are in a row coresponding to an individual week by week. The function
avgpts() calculates the best 7 of 10 scores by getting scores from right to
left in the spredsheet. I use Application.Caller.Row to tell the vb
program what row i am calculating and .column to telll it what column I am
starting in.
This works.
Next I added another sheet to the workbook and copied the data from the
avgpts sheet and substituted a slightly revised vb program with the
function =GHIN() to convert the entered scores to USGA handicap scores. I
needed to use the same Application.Caller.Row and
Application.Caller.Column to determine starting point in the program..
The program in the debugger will not go past Application.Caller.Row.

If I copy sheet to a new workbook, the function works. It does not like
to be married to the similar function in the same workbook.

Function Ghin()
'
' Ghin Macro
' Macro recorded 2/25/2005 by Charles Woll
'
On Error GoTo ErrorHandler
Row = 0
Col = 0


Total_plays = 10
Used_Plays = 8
Row = Application.Caller.Row
Col = Application.Caller.Column
i = 0
Cnt = 0
tot = 0
AvgPts = 0
FirstColumn = Range("First_Column").Column
Dim CellValue(10)

For i = 0 To 10 ' initialize CellValue
to 0
CellValue(i) = 0
Next i

For i = Col - 1 To FirstColumn + 1 Step -1 'steps from last to 1st
column


If Cells(Row, i) < "" Then
Cnt = Cnt + 1 'count cells with data
CellValue(Cnt - 1) = Cells(Row, i) 'Keep a total of
columns with data

End If


If i = FirstColumn + 1 Then ' last column with
data?
GoTo Sort
End If

If Cnt = Total_plays Then
GoTo Sort
End If
Next i

Sort:
For j = 0 To Cnt - 2
For k = j + 1 To Cnt - 1
If CellValue(j) = "" Then GoTo SumLowestPlays
If CellValue(k) = "" Then GoTo NextJ
If CellValue(j) CellValue(k) Then
temp = CellValue(j)
CellValue(j) = CellValue(k)
CellValue(k) = temp
End If
Next k
NextJ:
Next j


SumLowestPlays:

For i = 0 To Used_Plays - 1
tot = tot + CellValue(i)
Next i

If Cnt = Used_Plays Then Ghin = tot / Used_Plays Else Ghin = tot / Cnt
Ghin = Ghin * 0.96

Exit Function

ErrorHandler:
Ghin = "ERR"
Exit Function


End Function



"Bob Phillips" wrote in message
...
Well, you are using the Caller property? What is it that is triggering
this
code, that is what is the calling object? Some, such as a shape, don't
have
a Row property, so that code would fail.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Charles Woll" wrote in message
...
I am sorry, but I do not know what you mean. I am not very skilled at
this.
charlie

"Bob Phillips" wrote in message
...
Does your caller have a row property?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Charles Woll" wrote in message
...
I have 2 functions which call Row = Application.Caller.Row
The 2nd function bombs at this line. Any ideas?

\\\|///
\\ ~ ~ //
( @ @ )
--oOOo-(_)-oOOo---
Charlie Woll