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

anna wrote:

' 2. Named reference NB = number of blades in 360 deg ring.

If HI = 0 Or HI = NB Then

If NB is a named reference, I think that latter statement needs to be
something like:

If HI = 0 Or HI = Range("NB")

....or:

If HI = 0 Or HI = Worksheets(cond).Range("NB")

....depending on how you need to reference it. Why not just pass it as
a parameter to the function as well, rather than assume it's going to
be available?

Haven't tested it, don't know what NB is, but try this

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 = cond.Evaluate("Match(1,('" & cond & "'!B2:B200=" & HI & ")*" & _
"('" & cond & "'!A2:A200=" & mode & "), 0)")
gather = Application.Index(Worksheets(cond).Range("C2:C200" ), nrow, 0)

ElseIf HI 0 And HI < NB Then

nrow = cond.Evaluate("Match(1,('" & cond & "'!B2:B200=" & HI & ")*" & _
"('" & cond & "'!A2:A200=" & mode & "), 0)")
gather = Application.Index(Worksheets(cond).Range("C2:C200" ), nrow, 0)

End If

End Function

--
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

Thank you!

Anna

"Randy Harmelink" wrote:

anna wrote:

' 2. Named reference NB = number of blades in 360 deg ring.

If HI = 0 Or HI = NB Then

If NB is a named reference, I think that latter statement needs to be
something like:

If HI = 0 Or HI = Range("NB")

....or:

If HI = 0 Or HI = Worksheets(cond).Range("NB")

....depending on how you need to reference it. Why not just pass it as
a parameter to the function as well, rather than assume it's going to
be available?

Thanks so much!

Anna

"Bob Phillips" wrote:

Haven't tested it, don't know what NB is, but try this

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 = cond.Evaluate("Match(1,('" & cond & "'!B2:B200=" & HI & ")*" & _
"('" & cond & "'!A2:A200=" & mode & "), 0)")
gather = Application.Index(Worksheets(cond).Range("C2:C200" ), nrow, 0)

ElseIf HI 0 And HI < NB Then

nrow = cond.Evaluate("Match(1,('" & cond & "'!B2:B200=" & HI & ")*" & _
"('" & cond & "'!A2:A200=" & mode & "), 0)")
gather = Application.Index(Worksheets(cond).Range("C2:C200" ), nrow, 0)

End If

End Function

--
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