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

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