Excel VBA 设置变量以选择特定工作表中的一系列单元格

Excel VBA set variable to select a range of cells in a specific worksheet

提问by DimlyAware

I have been trying to run a script that goes through column C and trims out any spaces on the left or right of the text in the cell. When i run it I get a error regarding the set rr (ss, uu, vv as well when I try to run it).

我一直在尝试运行一个脚本，该脚本通过 C 列并删除单元格中文本左侧或右侧的任何空格。当我运行它时，我收到一个关于 set rr 的错误（ss、uu、vv 以及当我尝试运行它时）。

Here is where the debugger is finding the error "Run-time error '1004': Application-defined or object-defined error.

这是调试器发现错误“运行时错误‘1004’：应用程序定义或对象定义错误的地方。

Set rr = Worksheets("EOD").Range(Cells(2, 3), Cells(100, 3))

I also included the full code below just in case that might provide better insight. I have worked with JS and PHP but VBA is confusing the hell out of me.

我还在下面包含了完整的代码，以防万一可能会提供更好的见解。我使用过 JS 和 PHP，但是 VBA 把我搞糊涂了。

Thank you in advance for any help you can provide.

预先感谢您提供的任何帮助。

M

米

Sub Workbook_Open()
Dim r As Range
Dim s As Range
Dim t As Range
Dim u As Range
Dim v As Range
Dim a As Integer
Dim b As Integer
Dim c As Integer
Dim d As Integer
Dim e As Integer
Dim rr As Range
Dim ss As Range
Dim tt As Range
Dim uu As Range
Dim vv As Range
Set rr = Worksheets("EOD").Range(Cells(2, 3), Cells(100, 3))
Set ss = Worksheets("9AM Report").Range(Cells(2, 3), Cells(100, 3))
Set uu = Worksheets("1PM CST").Range(Cells(2, 3), Cells(100, 3))
Set vv = Worksheets("3PM ST").Range(Cells(2, 3), Cells(100, 3))
Set tt = Worksheets("11AM Report").Range(Cells(2, 3), Cells(100, 3))

For Each r In rr
    r = Trim(r)
    a = a + 1
    If a = 199 Then Call RefreshAllPivotTables
    If a > 200 Then End
Next
For Each s In ss
    s = Trim(s)
    b = b + 1
    If b = 199 Then Call RefreshAllPivotTables
    If b > 200 Then End
Next
For Each t In tt
    t = Trim(t)
    c = c + 1
    If c = 199 Then Call RefreshAllPivotTables
    If c > 200 Then End
Next
For Each u In uu
    u = Trim(u)
    d = d + 1
    If d = 199 Then Call RefreshAllPivotTables
    If d > 200 Then End
Next
For Each v In vv
    v = Trim(v)
    e = e + 1
    If e = 199 Then Call RefreshAllPivotTables
    If e > 200 Then End
Next
End Sub

Sub RefreshAllPivotTables()

Dim PT As PivotTable
Dim WS As Worksheet

For Each WS In ThisWorkbook.Worksheets

    For Each PT In WS.PivotTables
      PT.RefreshTable
    Next PT

Next WS

End Sub

回答by Portland Runner

I was able to reproduce your error when a different sheet was active. You need to fully define the sheet location of Cellslike you did for Range.

当不同的工作表处于活动状态时，我能够重现您的错误。您需要Cells像对Range.

Instead of:

代替：

Set rr = Worksheets("EOD").Range(Cells(2, 3), Cells(100, 3))

Try:

尝试：

Dim wks1 As Worksheet
Set wks1 = Worksheets("EOD")

Set rr = wks1.Range(wks1.Cells(2, 3), wks1.Cells(100, 3))