☰

VBA 脚本在所有工作表中循环，但不会在所有工作表中产生输出

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 worksheet-function microsoft-excel-2007 vba
VBA 脚本在所有工作表中循环，但不会在所有工作表中产生输出

我的这个 VBA 脚本循环遍历所有工作表，但我没有在所有工作表中看到输出。原因是什么？

Sub A()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
Dim ct As String
ct = ws.Range("H4").Text
If InStr(1, ct, "CELL") Then
For Each B In ws.Range("B7:B49").Cells
    If IsNumeric(B) And B <> "" Then
        Cells(B.Row, 9) = Trim(Cells(B.Row + 1, 8)) & Trim(Cells(B.Row + 2, 8))
        Cells(B.Row + 1, 8) = ""
        Cells(B.Row + 2, 8) = ""
    If B.Row > 50 Then Exit For
End If
Next B
For Each C In ws.Range("C1:C50").Cells
    If Cells(C.Row, C.Column - 1) = "" Then
        Cells(C.Row, C.Column) = ""
    End If
Next C
For Each D In ws.Range("D1:D50").Cells
    Cells(D.Row, D.Column) = ""
Next D
For Each E In ws.Range("E1:E50").Cells
    Cells(E.Row, E.Column) = ""
Next E
For Each F In ws.Range("F1:F50").Cells
    Cells(F.Row, F.Column) = ""
Next F
For Each G In ws.Range("G1:G50").Cells
    Cells(G.Row, G.Column) = ""
Next G
For Each D In ws.Range("D1:D50").Cells
    Cells(D.Row, D.Column) = Cells(D.Row, D.Column + 4)
    Cells(D.Row, D.Column + 4) = ""
Next D
For Each E In ws.Range("E1:E50").Cells
    Cells(E.Row, E.Column) = Cells(E.Row, E.Column + 4)
    Cells(E.Row, E.Column + 4) = ""
Next E
End If


Dim cat As String
cat = ws.Range("I4").Text
If InStr(1, cat, "CELL") Then
For Each B In ws.Range("B7:B49").Cells
    If IsNumeric(B) And B <> "" Then
        Cells(B.Row, 10) = Trim(Cells(B.Row + 1, 9)) & Trim(Cells(B.Row + 2, 9))
        Cells(B.Row + 1, 9) = ""
        Cells(B.Row + 2, 9) = ""
    If B.Row > 50 Then Exit For
End If
Next B
For Each C In ws.Range("C1:C50").Cells
    If Cells(C.Row, C.Column - 1) = "" Then
        Cells(C.Row, C.Column) = ""
    End If
Next C
For Each D In ws.Range("D1:D50").Cells
    Cells(D.Row, D.Column) = ""
Next D
For Each E In ws.Range("E1:E50").Cells
    Cells(E.Row, E.Column) = ""
Next E
For Each F In ws.Range("F1:F50").Cells
    Cells(F.Row, F.Column) = ""
Next F
For Each G In ws.Range("G1:G50").Cells
    Cells(G.Row, G.Column) = ""
Next G
For Each M In ws.Range("H1:H50").Cells
    Cells(M.Row, M.Column) = ""
Next M
For Each D In ws.Range("D1:D50").Cells
    Cells(D.Row, D.Column) = Cells(D.Row, D.Column + 5)
    Cells(D.Row, D.Column + 5) = ""
Next D
For Each E In ws.Range("E1:E50").Cells
    Cells(E.Row, E.Column) = Cells(E.Row, E.Column + 5)
    Cells(E.Row, E.Column + 5) = ""
Next E
End If



Dim cate As String
cate = ws.Range("J4").Text
If InStr(1, cate, "CELL") Then
For Each B In ws.Range("B7:B49").Cells
    If IsNumeric(B) And B <> "" Then
        Cells(B.Row, 11) = Trim(Cells(B.Row + 1, 10)) & Trim(Cells(B.Row + 2, 10))
        Cells(B.Row + 1, 10) = ""
        Cells(B.Row + 2, 10) = ""
    If B.Row > 50 Then Exit For
End If
Next B
For Each C In ws.Range("C1:C50").Cells
    If Cells(C.Row, C.Column - 1) = "" Then
        Cells(C.Row, C.Column) = ""
    End If
Next C
For Each D In ws.Range("D1:D50").Cells
    Cells(D.Row, D.Column) = ""
Next D
For Each E In ws.Range("E1:E50").Cells
    Cells(E.Row, E.Column) = ""
Next E
For Each F In ws.Range("F1:F50").Cells
    Cells(F.Row, F.Column) = ""
Next F
For Each G In ws.Range("G1:G50").Cells
    Cells(G.Row, G.Column) = ""
Next G
For Each M In ws.Range("H1:H50").Cells
    Cells(M.Row, M.Column) = ""
Next M
For Each I In ws.Range("I1:I50").Cells
    Cells(I.Row, I.Column) = ""
Next I
For Each J In ws.Range("J1:J50").Cells
    Cells(J.Row, J.Column) = ""
Next J
For Each D In ws.Range("D1:D50").Cells
    Cells(D.Row, D.Column) = Cells(D.Row, D.Column + 6)
    Cells(D.Row, D.Column + 6) = ""
Next D
For Each E In ws.Range("E1:E50").Cells
    Cells(E.Row, E.Column) = Cells(E.Row, E.Column + 6)
    Cells(E.Row, E.Column + 6) = ""
Next E
End If
Next ws
End Sub

它循环遍历工作表但却没有在 [ages

答案1

您可以从各个工作表中获取内容，但不会更改各个工作表的内容。

在您的脚本中，您引用了工作表的 ws，但后来您使用 Cells(..) 来设置其内容。您需要在其前面添加 ws.，因此您的代码显示为 ws.cells(..., ...) = ""

相关内容