Method 'Union' of object '_Global'

Question

As far as I can tell, I have everything declared correctly (albeit complicated), I just need a fresh set of eyes. According to the compiler my "MultiForm1" union is incorrect:

Sub ShiftMaster()
'Declarations
Dim Mon As Range, Tue As Range, Wed As Range, Thu As Range, Fri As Range, Sat As Range, Sun As Range, _
 Multi1 As Range, Multi2 As Range, Multi3 As Range, Multi4 As Range, Multi5 As Range, Multi6 As Range, _
 DayNum As Range, MonFormOne As Range, MonFormTwo As Range, MonFormThree As Range, TueFormOne As Range, _
 TueFormTwo As Range, TueFormThree As Range, WedFormOne As Range, WedFormTwo As Range, WedFormThree As Range, _
 ThuFormOne As Range, ThuFormTwo As Range, ThuFormThree As Range, FriFormOne As Range, FriFormTwo As Range, _
 FriFormThree As Range, SatFormOne As Range, SatFormTwo As Range, SatFormThree As Range, SunFormOne As Range, _
 SunFormTwo As Range, SunFormThree As Range, ShiftNum As Range, MultiForm1 As Range, MultiForm2 As Range, _
 MultiForm3 As Range, MultiForm4 As Range, MultiForm5 As Range, MultiForm6 As Range, MultiForm7 As Range

Set Mon = Sheets("Production").Range("B3:F26")
Set Tue = Sheets("Production").Range("K3:O26")
Set Wed = Sheets("Production").Range("T3:X26")
Set Thu = Sheets("Production").Range("AC3:AG26")
Set Fri = Sheets("Production").Range("AL3:AP26")
Set Sat = Sheets("Production").Range("AU3:AY26")
Set Sun = Sheets("Production").Range("BD3:BH26")
Set Multi1 = Union(Mon, Tue, Wed, Thu, Fri, Sat, Sun)
Set Multi2 = Union(Tue, Wed, Thu, Fri, Sat, Sun)
Set Multi3 = Union(Wed, Thu, Fri, Sat, Sun)
Set Multi4 = Union(Thu, Fri, Sat, Sun)
Set Multi5 = Union(Fri, Sat, Sun)
Set Multi6 = Union(Sat, Sun)
Set DayNum = Sheets("Command Console").Range("J22")
Set MonFormOne = Sheets("Production").Range("H9")
Set MonFormTwo = Sheets("Production").Range("H17")
Set MonFormThree = Sheets("Production").Range("H25")
Set TueFormOne = Sheets("Production").Range("H33")
Set TueFormTwo = Sheets("Production").Range("H41")
Set TueFormThree = Sheets("Production").Range("H49")
Set WedFormOne = Sheets("Production").Range("H57")
Set WedFormTwo = Sheets("Production").Range("H65")
Set WedFormThree = Sheets("Production").Range("H73")
Set ThuFormOne = Sheets("Production").Range("H81")
Set ThuFormTwo = Sheets("Production").Range("H89")
Set ThuFormThree = Sheets("Production").Range("H97")
Set FriFormOne = Sheets("Production").Range("H105")
Set FriFormTwo = Sheets("Production").Range("H113")
Set FriFormThree = Sheets("Production").Range("H121")
Set SatFormOne = Sheets("Production").Range("H129")
Set SatFormTwo = Sheets("Production").Range("H137")
Set SatFormThree = Sheets("Production").Range("H145")
Set SunFormOne = Sheets("Production").Range("H153")
Set SunFormTwo = Sheets("Production").Range("H161")
Set SunFormThree = Sheets("Production").Range("H169")
Set ShiftNum = Sheets("Command Console").Range("J24")
**Set MultiForm1 = Union(MonFormOne, MonFormTwo, MonFormThree, TueFormOne, TueFormTwo, TwoFormThree, WedFormOne, WedFormTwo, WedFormThree, _
 ThuFormOne, ThuFormTwo, ThuFormThree, FriFormOne, FriFormTwo, FriFormThree, SatFormOne, SatFormTwo, SatFormThree, SunFormOne, SunFormTwo, SunFormThree)**
Set MultiForm2 = Union(TueFormOne, TueFormTwo, TwoFormThree, WedFormOne, WedFormTwo, WedFormThree, ThuFormOne, ThuFormTwo, ThuFormThree, _
 FriFormOne, FriFormTwo, FriFormThree, SatFormOne, SatFormTwo, SatFormThree, SunFormOne, SunFormTwo, SunFormThree)
Set MultiForm3 = Union(WedFormOne, WedFormTwo, WedFormThree, ThuFormOne, ThuFormTwo, ThuFormThree, FriFormOne, FriFormTwo, FriFormThree, _
 SatFormOne, SatFormTwo, SatFormThree, SunFormOne, SunFormTwo, SunFormThree)
Set MultiForm4 = Union(ThuFormOne, ThuFormTwo, ThuFormThree, FriFormOne, FriFormTwo, FriFormThree, SatFormOne, SatFormTwo, SatFormThree, _
 SunFormOne, SunFormTwo, SunFormThree)
Set MultiForm5 = Union(FriFormOne, FriFormTwo, FriFormThree, SatFormOne, SatFormTwo, SatFormThree, SunFormOne, SunFormTwo, SunFormThree)
Set MultiForm6 = Union(SatFormOne, SatFormTwo, SatFormThree, SunFormOne, SunFormTwo, SunFormThree)
Set MultiForm7 = Union(SunFormOne, SunFormTwo, SunFormThree)

'###########################################################################################################
'#                              Selects cells for format deletion, calls deleter                           #
'###########################################################################################################

Sheets("Production").Select
ActiveSheet.Unprotect
If DayNum = 1 Then
Multi1.Select
ElseIf DayNum = 2 Then
Multi2.Select
ElseIf DayNum = 3 Then
Multi3.Select
ElseIf DayNum = 4 Then
Multi4.Select
ElseIf DayNum = 5 Then
Multi5.Select
ElseIf DayNum = 6 Then
Multi6.Select
ElseIf DayNum = 7 Then
Sun.Select
Else
MsgBox ("There is a problem with the day number formula")
End If
Call BorderBlaster

'###########################################################################################################
'#                                          End Format deletion                                            #
'###########################################################################################################

'###########################################################################################################
'#                            Chooses latent formulas for deletion, calls deleter                          #
'###########################################################################################################

If DayNum = 1 Then
MultiForm1.Select
ElseIf DayNum = 2 Then
MultiForm2.Select
ElseIf DayNum = 3 Then
MultiForm3.Select
ElseIf DayNum = 4 Then
MultiForm4.Select
ElseIf DayNum = 5 Then
MultiForm5.Select
ElseIf DayNum = 6 Then
MultiForm6.Select
ElseIf DayNum = 7 Then
MultiForm7.Select
Else
MsgBox ("There is a problem with the day number formula")
End If

Call FormulaBlaster

'###########################################################################################################
'#                                          End Formula Deletion                                           #
'###########################################################################################################

Call BorderDamon

Call FormulaDamon

Call LastRights

End Sub
Sub BorderBlaster()
'Clears borders
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlDouble
        .ThemeColor = 5
        .TintAndShade = -0.499984740745262
        .Weight = xlThick
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlDouble
        .ThemeColor = 9
        .TintAndShade = -0.499984740745262
        .Weight = xlThick
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlDouble
        .ThemeColor = 9
        .TintAndShade = -0.499984740745262
        .Weight = xlThick
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlDouble
        .ThemeColor = 9
        .TintAndShade = -0.499984740745262
        .Weight = xlThick
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With
End Sub
Sub FormulaBlaster()
'Clears formulas
Selection.ClearContents

End Sub
Sub BorderDamon()
'Some Code
End Sub
Sub FormulaDamon()
'Some Code
End Sub
Sub LastRights()
'Some Code
End Sub

Obviously the code is incomplete. Does anyone have any ideas why this would be causing this?

Answer 1

OT: In an effort to clean up some of your code to be a little more reafable, you might consider looping through a For Each...Next Statement with the Union method collecting the cells on each iteration of the loop. A bulk value (or range) assignment would be preferable if the cells were contiguous but the stagger with Union requires a loop.

Here are two examples for paring down all the code and variables that are currently required to construct and assign the MultiForm1 variable.

Option Explicit

Dim MultiForm1 as Range

'Option1: do it in groups of three on a 24 row stagger
With Worksheets("Production")
    With .Range("H9")
        Set MultiForm1 = .Cells(1)  '<~~ seed MultiForm1
        For h = 0 To 144 Step 24
            Set MultiForm1 = Union(MultiForm1, .Offset(h, 0), .Offset(h + 8, 0), .Offset(h + 16, 0))
        Next h
    End With
End With
Debug.Print MultiForm1.Address(0, 0, external:=True)

'Option2: just cycle through on the 8 row stagger
With Worksheets("Production")
    With .Range("H9")
        Set MultiForm1 = .Cells(1)  '<~~ seed MultiForm1
        For h = 0 To 162 Step 8
            Set MultiForm1 = Union(MultiForm1, .Offset(h, 0))
        Next h
    End With
End With
Debug.Print MultiForm1.Address(0, 0, external:=True)

It's always a good idea to keep an eye out for patterns that can be exploited. You probably aren't getting paid by the line for your code.

Less typing == less typos.

Answer 2

You need to go into Tools/Options and select the option to Require Variable Declaration. This will place Option Explicit at the start of any new module you insert.

Had you done this, you would have quickly found your error, as well as the similar errors in other of your Set statements.

Set MultiForm1 = Union(MonFormOne, MonFormTwo, MonFormThree, TueFormOne, TueFormTwo, **TwoFormThree**, WedFormOne, WedFormTwo, WedFormThree, _
 ThuFormOne, ThuFormTwo, ThuFormThree, FriFormOne, FriFormTwo, FriFormThree, SatFormOne, SatFormTwo, SatFormThree, SunFormOne, SunFormTwo, SunFormThree)