时间戳

首页 > 解决方案 > 尝试在 Range 对象中使用字符串对象时，对象 _global 的方法范围失败

excel - 尝试在 Range 对象中使用字符串对象时，对象 _global 的方法范围失败

问题描述

尝试使用范围对象和字符串的组合时，我收到“对象_global 的方法范围失败”消息。我正在尝试遍历多个工作表，复制并转置工作表中的每一行，将转置的行堆叠成一列，然后移动到下一张表以获取其行并将它们粘贴到下一列。

Dim CopyRng, pasteRng, Outnum, compsht As String
Dim myRng, PstRng As Range

For j = 5 To 10     
   For i = 1 To tot_centers
     Outnum = "out" & j
     CopyRng = "ThisWorkbook.Sheets(""" & Outnum & """).Cells(" & i & ", 2), 
           ThisWorkbook.Sheets(""" & Outnum & """).Cells(" & i & ", " & tot_days + 2 & ")"
     Set myRng = Range(CopyRng)
     Sheets(Outnum).Range(myRng).Copy  'THIS IS WHERE DEBUG HITS ERROR

     pasteRng = "ThisWorkbook.Sheets(""" & "Compiled Data" & """).Cells(" & ((tot_days * (i - 1)) + (i + 1)) & ", " & j - 2 & ")"
     Set PstRng = Range(pasteRng)  'AND I'LL PROBABLY GET THE SAME ERROR HERE
     PstRng.PasteSpecial Transpose:=True
  Next i
Next j

标签： excelvba

解决方案

对于看到这篇文章并想知道如何打开工作表、一次抓取多行并将它们堆叠成单独列的任何人，代码结果如下：

Dim Outnum As String

For j = 5 To 10     'use worksheets with outputs 5 through 10
    For i = 1 To tot_centers  'in this case, tot_centers was declared earlier in the code and is and integer I count from a sheet
        Outnum = "out" & j
        Sheets(Outnum).Range(Sheets(Outnum).Cells(i, 2), Sheets(Outnum).Cells(i, tot_days + 2)).Copy
        Sheets("Compiled Data").Range(Sheets("Compiled Data").Cells((tot_days * (i - 1)) + (i + 1), j - 1), Sheets("Compiled Data").Cells((tot_days * (i - 1)) + (i + 1), j - 1)).PasteSpecial Transpose:=True
    Next i
Next j

End Sub

推荐阅读