vba help - Union of Object global failed.

[Mallesh23]

Hi Team,

I am trying to use union method but data is in different workbook. is this works
Getting Error message -----> Method Union of Object global failed.


if Data is in single workbook and single sheets ............ this line of code worked.
For Each area In Union([A1].CurrentRegion, [d1].CurrentRegion).Areas

replacing above working line into below code.
But if data is in Different workbook. these below two lines not working. throwing error.
For Each area In Union(wbk_s1.Worksheets(1).Range("A1").CurrentRegion, wbk_S2.Worksheets(1).Range("A1").CurrentRegion).Areas
or
For Each area In Union(rg1, rg2).Areas


My attempted code

Sub Add_TwoSet_Data_Into_Dictionary()

    Dim wbk_s1 As Workbook
    Dim wbk_S2 As Workbook
    
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    
    Dim rg1 As Range
    Dim rg2 As Range
    
    Set wbk_s1 = Workbooks.Open("D:\Book1\Book1.XLSX", False, True)
    Set wbk_S2 = Workbooks.Open("D:\Book1\Book2.XLSX", False, True)


    Set ws1 = wbk_s1.Worksheets(1)
    Set ws2 = wbk_S2.Worksheets(1)
    

    Set rg1 = ws1.Range("A1").CurrentRegion
    Set rg2 = ws2.Range("A1").CurrentRegion
     

    Dim dict As New Scripting.Dictionary
    Dim i As Long
    Dim area As Range
    Dim arr As Variant
    Dim skey As String
   
    With dict
         ' If Data is in Single worksheet thisline works
        'For Each area In Union([A1].CurrentRegion, [d1].CurrentRegion).Areas
    
        'if data is two different workbook it is not working
       ' For Each area In Union(wbk_s1.Worksheets(1).Range("A1").CurrentRegion, wbk_S2.Worksheets(1).Range("A1").CurrentRegion).Areas
        
        For Each area In Union(rg1, rg2).Areas
        
        'For Each area In Union([A1].CurrentRegion, [d1].CurrentRegion).Areas
            arr = area.Value
            For i = LBound(arr, 1) To UBound(arr, 1)
                skey = arr(i, 2) & "|" & arr(i, 7)
                If Not .Exists(skey) Then
                    .Add skey, Array(arr(i, 3), arr(i, 8))
                End If
            Next i
        Next area
       
    End With

End Sub

Thanks
mg

 

[steve the fish]

You cant do a union on different worksheets hence the error.

 

[Mallesh23]

Hi Steve,

Thanks for the confirmation ! ?



Thanks
mg