Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simplify CrossJoin function
Can anyone help me simplify the following function? The object references
seem too complicated (eg ActiveSheet.Range(area1.Address).Cells ) and I can't get the output bit to work. Any ideas? Thanks Damien Public Function CrossJoin(area1 As Range, area2 As Range, Optional output_range) 'Cartesian Join two areas 'On Error GoTo CrossJoin_Err Dim cll1 Dim cll2 Dim i As Long For Each cll1 In ActiveSheet.Range(area1.Address).Cells For Each cll2 In ActiveSheet.Range(area2.Address).Cells Debug.Print cll1, cll2 If Not IsMissing(output_range) Then '!!TODO ActiveSheet.Range(output_range.Address).Value = cll1.Value End If i = i + 1 Next Next CrossJoin = i CrossJoin_Exit: Exit Function CrossJoin_Err: MsgBox Err & ", " & Err.Description Resume CrossJoin_Exit End Function |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Simplify CrossJoin function
since you are passing in area1 and area1 and output as ranges they already
know what sheet they are on. Public Function CrossJoin(area1 As Range, area2 As Range, Optional output_range) 'Cartesian Join two areas 'On Error GoTo CrossJoin_Err Dim cll1 as Range Dim cll2 as Range Dim i As Long For Each cll1 In area1.Cells For Each cll2 In area2.Cells Debug.Print cll1, cll2 If Not IsMissing(output_range) Then '!!TODO output_range.Value = cll1.Value End If i = i + 1 Next Next CrossJoin = i CrossJoin_Exit: Exit Function CrossJoin_Err: MsgBox Err & ", " & Err.Description Resume CrossJoin_Exit End Function Not sure what a cartesion join is, but perhaps you want the intersection or the union On error resume next set rng = intersect(area1,area2) On Error goto 0 set output_range = rng -- Regards, Tom Ogilvy "Damien" wrote in message ... Can anyone help me simplify the following function? The object references seem too complicated (eg ActiveSheet.Range(area1.Address).Cells ) and I can't get the output bit to work. Any ideas? Thanks Damien Public Function CrossJoin(area1 As Range, area2 As Range, Optional output_range) 'Cartesian Join two areas 'On Error GoTo CrossJoin_Err Dim cll1 Dim cll2 Dim i As Long For Each cll1 In ActiveSheet.Range(area1.Address).Cells For Each cll2 In ActiveSheet.Range(area2.Address).Cells Debug.Print cll1, cll2 If Not IsMissing(output_range) Then '!!TODO ActiveSheet.Range(output_range.Address).Value = cll1.Value End If i = i + 1 Next Next CrossJoin = i CrossJoin_Exit: Exit Function CrossJoin_Err: MsgBox Err & ", " & Err.Description Resume CrossJoin_Exit End Function |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Custom function to simplify Index(match)) formula | Excel Discussion (Misc queries) | |||
Help me simplify this function.... | Excel Worksheet Functions | |||
simplify a vlookup function | Excel Worksheet Functions | |||
simplify function | Excel Worksheet Functions | |||
Simplify Vlookup function in Excel | Excel Worksheet Functions |