Functions and Ranges
 

I have a function that needs the input of 2 ranges say a1:a5 and ba:b5

how can I set this function up so I can drag over the ranges when
using the funciton in a work book (similar to what happens in
Vlookup). As it stands I have to input each cell individually here is
the start of the code

Function Sw_hyp(P1, P2, p3, p4, p5, sw1, sw2, sw3, sw4, sw5, Pc_hyp)

If sw5 = 0 Then
sample = 4
x5 = 0
y5 = 0
Else: sample = 5
End If

The if statement is there because I sometimes use 4 elements and
sometimes 5

Ive go the guts of the function working and returning correct values I
wanted to make it easier to use in the workbook

Cheers
Ian Mangelsdorf

Functions and Ranges
 

Hello Ian,

You can use "ParamArray" something like this.

Function Sw_hyp(ByVal Target As Range, ParamArray Target2())
Dim c As Range
Dim i As Long
Dim str As String

For Each c In Target
str = str & c.Value
Next

For i = 0 To UBound(Target2)
For Each c In Target2(i)
str = str & c.Value
Next
Next
Sw_hyp = str
End Function


--
Kind Regards
Colo
/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
Colo of 'The Road of The Cell Masters' :)

URL:http://www.interq.or.jp/sun/puremis/...astersLink.htm


/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/

"Ian Mangelsdorf" wrote in message
om...
I have a function that needs the input of 2 ranges say a1:a5 and ba:b5

how can I set this function up so I can drag over the ranges when
using the funciton in a work book (similar to what happens in
Vlookup). As it stands I have to input each cell individually here is
the start of the code

Function Sw_hyp(P1, P2, p3, p4, p5, sw1, sw2, sw3, sw4, sw5, Pc_hyp)

If sw5 = 0 Then
sample = 4
x5 = 0
y5 = 0
Else: sample = 5
End If

The if statement is there because I sometimes use 4 elements and
sometimes 5

Ive go the guts of the function working and returning correct values I
wanted to make it easier to use in the workbook

Cheers
Ian Mangelsdorf