对象"_Global"的方法“范围"失败 [英] Method 'Range' of object '_Global' failed

问题描述

我试图遍历三个下拉列表中的项目，命名范围分别为A，B和C.Summary输出根据所选下拉列表中的项目而变化.我想复制每个摘要输出(一个19行乘15列的表)并将其粘贴到新的工作表中(表3).

I am trying to loop through the items in three drop down lists, with the named ranges A, B, and C. The Summary output changes based on the items in the drop down list chosen. I would like to copy each summary output (a table of 19 rows by 15 columns) and paste them into a new Sheet (Sheet 3).

例如，如果我在列表A中有3个项目，在列表B中有2个项目，在列表C中有2个项目，那么我总共需要将12个输出(3 * 2 * 2)粘贴到工作表3中

For example, if I had 3 items in List A, 2 items in List B, and 2 items in List C, I would have a total of 12 outputs that I need (3*2*2) pasted into Sheet 3.

运行此代码时，出现以下错误:

When I run this code, I get the following error:

对象'_Global'的方法'Range'失败

Method 'Range' of object '_Global' failed

我将不胜感激！我是VBA编程的新手，所以我敢肯定，有一种更有效的方法可以做到这一点...

I would appreciate any help possible! I'm a newbie to VBA programming, so I'm sure there's a more efficient way to do this...

谢谢！

Sub SummarizeData()
    '
    ' SummarizeData Macro
    '

    Dim rngCeded As Range
    Dim rngTF As Range
    Dim rngFX As Range
    Dim LOS As Integer

    Set rngCeded = Range("A")
    Set rngTF = Range("B")
    Set rngFX = Range("C")

    LOS = 19
    For n = 1 To 12
        For Each i In Range("A")
            For Each j In Range("B")
                For Each k In Range("C")
                    Sheets("Summary").Range("SummaryData").Copy
                    Sheets("Sheet3").Range("E5").Offset(i - 1, 0).PasteSpecial Paste:=xlPasteValues
                Next k
            Next j
        Next i
    n = n + LOS
    Next n
    End Sub

推荐答案

由于未指定范围的来源(Set rng = Range("..."))，因此代码的结果取决于调用该方法的上下文.

Since you do not specify where the Range comes from (Set rng = Range("...")), the outcome of the code depends on the context you call the method.

最好调用特定对象的Range方法.在您的情况下，这可能是一个工作表对象，类似于此:

It is better to call the Range method of a specific object. In your case, that might be a Worksheet-object, similar to this:

' ...
Dim MySheet As Excel.Worksheet
Set MySheet = ActiveWorkbook.Sheets("MySheet")
Set rngCeded = MySheet.Range("A") 
' ...

这篇关于对象"_Global"的方法“范围"失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！