Simplify CrossJoin function

[Damien]

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

 

[Tom Ogilvy]

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