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
|