I am trying to write my first UDF and am hitting a snag. I need to pass a
worksheet name as one of the arguments (referenced in a cell). Function keeps
giving me zeros! I have multiple worksheets with result data and in a summary
sheet I want to use my UDF to "gather" this info in a clear concise manner.
The worksheet function I had was waaaayyy to big, but worked. That's why I
was trying the UDF approach. Here's the UDF:

Function gather(cond As String, HI As Integer, mode As Integer) As Double

'-----------------------------------------------------------------
' Purpose: Collect data from multiple results sheets
'
'
' Arguments:
'
' cond ... Engine condition
'
' HI ... Harmonic Index
'
' mode ... mode number
'
' Assumptions/Restrictions:
'
' 1. Assumes the results are stored in separate worksheets named
' as the conditions.
'
' 2. Named reference NB = number of blades in 360 deg ring.
'
' Notes:
'
' Revision History:
'
' Date By Description
'
'-----------------------------------------------------------------
Dim nrow As Integer

If HI = 0 Or HI = NB Then

nrow = Application.WorksheetFunction.Match(1,
(Worksheets(cond).Range("B2:B200") = HI) * (Worksheets(cond).Range("A2:A200")
= mode), 0)

gather =
Application.WorksheetFunction.Index(Worksheets(con d).Range("C2:C200"), nrow,
0)

ElseIf HI 0 And HI < NB Then

nrow = Application.WorksheetFunction.Match(1,
(Worksheets(cond).Range("B2:B200") = HI) * (Worksheets(cond).Range("A2:A200")
= (mode * 2)), 0)

gather =
Application.WorksheetFunction.Index(Worksheets(con d).Range("C2:C200"), nrow,
0)

End If

End Function

Any suggestions??
Thanks in advance,
Anna

see response in programming

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"anna" wrote in message
...
I am trying to write my first UDF and am hitting a snag. I need to pass a
worksheet name as one of the arguments (referenced in a cell). Function
keeps
giving me zeros! I have multiple worksheets with result data and in a
summary
sheet I want to use my UDF to "gather" this info in a clear concise
manner.
The worksheet function I had was waaaayyy to big, but worked. That's why I
was trying the UDF approach. Here's the UDF:

Function gather(cond As String, HI As Integer, mode As Integer) As Double

'-----------------------------------------------------------------
' Purpose: Collect data from multiple results sheets
'
'
' Arguments:
'
' cond ... Engine condition
'
' HI ... Harmonic Index
'
' mode ... mode number
'
' Assumptions/Restrictions:
'
' 1. Assumes the results are stored in separate worksheets named
' as the conditions.
'
' 2. Named reference NB = number of blades in 360 deg ring.
'
' Notes:
'
' Revision History:
'
' Date By Description
'
'-----------------------------------------------------------------
Dim nrow As Integer

If HI = 0 Or HI = NB Then

nrow = Application.WorksheetFunction.Match(1,
(Worksheets(cond).Range("B2:B200") = HI) *
(Worksheets(cond).Range("A2:A200")
= mode), 0)

gather =
Application.WorksheetFunction.Index(Worksheets(con d).Range("C2:C200"),
nrow,
0)

ElseIf HI 0 And HI < NB Then

nrow = Application.WorksheetFunction.Match(1,
(Worksheets(cond).Range("B2:B200") = HI) *
(Worksheets(cond).Range("A2:A200")
= (mode * 2)), 0)

gather =
Application.WorksheetFunction.Index(Worksheets(con d).Range("C2:C200"),
nrow,
0)

End If

End Function

Any suggestions??
Thanks in advance,
Anna