vba help - Union of Object global failed.

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
863
Office Version
  1. 2010
Platform
  1. Windows
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

VBA 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
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,388
Office Version
  1. 365
Platform
  1. Windows
You cant do a union on different worksheets hence the error.
 

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
863
Office Version
  1. 2010
Platform
  1. Windows
Hi Steve,

Thanks for the confirmation ! 🕺 (y)



Thanks
mg
 

Watch MrExcel Video

Forum statistics

Threads
1,129,666
Messages
5,637,657
Members
416,979
Latest member
juliegeorge792

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top