在同一工作表上的单元格上，对象"_Global"的方法"Union"失败 [英] Method 'Union' of object '_Global' failed on cells that are on the same sheet

问题描述

据我所知，我已经正确声明了所有内容(尽管很复杂)，但我只需要换一组新的眼睛即可.根据编译器，我的"MultiForm1"联合是不正确的:

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?

推荐答案

您需要进入工具/选项"，然后选择要求变量声明"选项.这会将 Option Explicit 放置在您插入的任何新模块的开头.

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.

完成此操作后，您将很快发现错误以及其他Set语句中的类似错误.

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)

这篇关于在同一工作表上的单元格上，对象"_Global"的方法"Union"失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！