K
Kieranz
Hi All
Not long Newbie in VBA, using XP with XL03.
Q1: I have 2 subtotal cells (say D10, E10) and a total cell (say G10).
Cells D10 and E10 can contain a numeric, text or "" (null string) as
default. My UDF is as follows:
Function AddFB(x, y) 'front and back score in golf
Dim a, b
a = Range(x)
b = Range(y)
If IsNumeric(a) And IsNumeric(b) Then
AddFB = a + b
ElseIf Len(a) = 0 Then 'to check for ""
AddFB = b 'whatever value in b eg number or text
ElseIf Len(b) = 0 Then
AddFB = a 'again number or text
ElseIf istext(a) then
AddFB = a
ElseIf istext(b) then
AddFB = b
End If
End Function
Whats wrong because when I have my formula below in G10 it works.
'=IF(AND(ISNUMBER(D10),ISNUMBER(E10)),SUM(D10,E10),IF(LEN(D10)=0, _
E10,IF(LEN(E10)=0,D10,IF(ISTEXT(D10),D10,IF(ISTEXT(E10),E10)))))
Background (specially golfers!!). Cell D10 is Front9 score and E10 is
Back9.
A golfer may have besides his shots (ie numeric), text eg n=NR (no
return), d=DQ (disqualified) or r-RTD (Retired). Therefore if text
overrides the scores BUT if there is a "" and a score then of course
score is counted. I hope this makes senses.
Summarised:
x y Result
n n x+y
n t y ie pickup text
t n x
"" n/t y ie pickup n or t
n/t "" x
Q2. I have a couple of UDFs BUT when I do into debug for any of my
Subs the debug goes to one of the various UDF and I can't debug (F8) my
codes. Also how do I debug UDFs line by line?
For those curious about my other UDFs, they are below:
Many, many thks. I am enjoying but it gets a hell lot fustrating when
in the cell you have UDF it goes ###. Improvements, with guide or
reason
being cheeky most welcome.
God bless and Rgds KN
Here's the other UDFs:
Function GScore(HSc As Range) 'select 9holes scores
Dim i As Long
For i = 1 To HSc.Cells.Count
If HSc(1) = "" Then
GScore = ""
Exit Function
End If
If HSc(i) = "d" Then
GScore = "DQ"
Exit Function
End If
If HSc(i) = "n" Then
GScore = "NR"
Exit Function
End If
If HSc(i) = "r" Then
GScore = "RTD"
Exit Function
End If
GScore = GScore + HSc(i) 'add the scores
Next i
End Function
Function NetF9(GSc As Range, HC As Range) 'front9 net score, gross
- ½ handicap
If GSc = "" Then
NetF9 = ""
Exit Function
End If
If WorksheetFunction.IsText(GSc) Then
NetF9 = GSc
Else
NetF9 = GSc - (WorksheetFunction.RoundUp(HC / 2, 0))
End If
End Function
Function NetB9(GSc As Range, HC As Range)
If GSc = "" Then
NetB9 = ""
Exit Function
End If
If WorksheetFunction.IsText(GSc) Then
NetB9 = GSc
Else
NetB9 = GSc - (HC - (WorksheetFunction.RoundUp(HC / 2, 0)))
End If
End Function
Not long Newbie in VBA, using XP with XL03.
Q1: I have 2 subtotal cells (say D10, E10) and a total cell (say G10).
Cells D10 and E10 can contain a numeric, text or "" (null string) as
default. My UDF is as follows:
Function AddFB(x, y) 'front and back score in golf
Dim a, b
a = Range(x)
b = Range(y)
If IsNumeric(a) And IsNumeric(b) Then
AddFB = a + b
ElseIf Len(a) = 0 Then 'to check for ""
AddFB = b 'whatever value in b eg number or text
ElseIf Len(b) = 0 Then
AddFB = a 'again number or text
ElseIf istext(a) then
AddFB = a
ElseIf istext(b) then
AddFB = b
End If
End Function
Whats wrong because when I have my formula below in G10 it works.
'=IF(AND(ISNUMBER(D10),ISNUMBER(E10)),SUM(D10,E10),IF(LEN(D10)=0, _
E10,IF(LEN(E10)=0,D10,IF(ISTEXT(D10),D10,IF(ISTEXT(E10),E10)))))
Background (specially golfers!!). Cell D10 is Front9 score and E10 is
Back9.
A golfer may have besides his shots (ie numeric), text eg n=NR (no
return), d=DQ (disqualified) or r-RTD (Retired). Therefore if text
overrides the scores BUT if there is a "" and a score then of course
score is counted. I hope this makes senses.
Summarised:
x y Result
n n x+y
n t y ie pickup text
t n x
"" n/t y ie pickup n or t
n/t "" x
Q2. I have a couple of UDFs BUT when I do into debug for any of my
Subs the debug goes to one of the various UDF and I can't debug (F8) my
codes. Also how do I debug UDFs line by line?
For those curious about my other UDFs, they are below:
Many, many thks. I am enjoying but it gets a hell lot fustrating when
in the cell you have UDF it goes ###. Improvements, with guide or
reason
being cheeky most welcome.God bless and Rgds KN
Here's the other UDFs:
Function GScore(HSc As Range) 'select 9holes scores
Dim i As Long
For i = 1 To HSc.Cells.Count
If HSc(1) = "" Then
GScore = ""
Exit Function
End If
If HSc(i) = "d" Then
GScore = "DQ"
Exit Function
End If
If HSc(i) = "n" Then
GScore = "NR"
Exit Function
End If
If HSc(i) = "r" Then
GScore = "RTD"
Exit Function
End If
GScore = GScore + HSc(i) 'add the scores
Next i
End Function
Function NetF9(GSc As Range, HC As Range) 'front9 net score, gross
- ½ handicap
If GSc = "" Then
NetF9 = ""
Exit Function
End If
If WorksheetFunction.IsText(GSc) Then
NetF9 = GSc
Else
NetF9 = GSc - (WorksheetFunction.RoundUp(HC / 2, 0))
End If
End Function
Function NetB9(GSc As Range, HC As Range)
If GSc = "" Then
NetB9 = ""
Exit Function
End If
If WorksheetFunction.IsText(GSc) Then
NetB9 = GSc
Else
NetB9 = GSc - (HC - (WorksheetFunction.RoundUp(HC / 2, 0)))
End If
End Function