Excle VBA:运行时1004，使用联合将范围添加到预先存在的范围(它们在同一张纸上！) [英] Excle VBA: Runtime 1004 using Union to add range to pre-existing range (they're on the same sheet!)

问题描述

Excel VBA:我正在尝试使用Application.Union将范围添加到现有范围.每次Union进行尝试时，都会出现运行时错误1004.缺点是范围在同一张纸上，所以我很茫然.

Excel VBA: I'm trying to add a range to a pre-existing range using Application.Union. Every time the Union makes the attempt, I get runtime error 1004. The kicker is that the ranges are on the same sheet, so I'm at a loss.

魔术发生在嵌套的FOR循环中的IF块中.我为上下文添加了更多代码.任何帮助将不胜感激！

The magic is happening in the IF block within the nested FOR loop. I've included a bit more code for context. Any help would be greatly appreciated!

代码示例:

'---------------------------------------
'Get range of battle1 IDs from Sessions
'---------------------------------------
Dim ws As Worksheet: Set ws = objSessionsWB.ActiveSheet
Set rngBattleIDs = ws.Range("E3", ws.Range("E3").End(xlDown))

'---------------------------------------------------------
'Get range of battles from Battles that match battle1 IDs
'---------------------------------------------------------
Set ws = objBattlesWB.ActiveSheet

Dim rngBattleStats As Range
Dim bRow As Range

For Each battle In ws.Range("A3", ws.Range("A3").End(xlDown))
    For Each battleID In rngBattleIDs
        If battleID.Value = battle.Value Then

            'Get row containing battle1 info
            Set bRow = ws.Range(battle.End(xlToLeft), battle.End(xlToRight))

            'Use Union to add additional rows to range
            If rngBattleStats Is Nothing Then
                Set rngBattleStats = bRow
            Else
                Set rngBattleStats = Union(rngBattleStats, bRow)
            End If

        End If
    Next
Next

推荐答案

问题似乎不在我的问题范围内.该脚本正在workbook1中运行，但是我正在使用的范围在workbook2中.解决方案是从workbook2运行Union:

Looks like the problem was outside the scope of my question. The script is being run in workbook1, but the ranges I'm working with are in workbook2. The solution was to run Union from workbook2:

Set rngBattleStats = workbook2.Application.Union(rngBattleStats, bRow)

这篇关于Excle VBA:运行时1004，使用联合将范围添加到预先存在的范围(它们在同一张纸上！)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！