Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple IF functions, different ranges | Excel Discussion (Misc queries) | |||
If functions Looking at ranges | Excel Discussion (Misc queries) | |||
IF functions and Names Ranges | Excel Discussion (Misc queries) | |||
Copy and SUM functions with different ranges | Excel Worksheet Functions | |||
if functions with more than 7 ifs (ranges are possible?) | Excel Worksheet Functions |