文档建议反馈控制台
首页
学习
活动
专区
圈层
工具
MCP广场
文章/答案/技术大牛
发布
首页
学习
活动
专区
圈层
工具
MCP广场
返回腾讯云官网
社区首页 >问答首页 >带循环的VBA - PB

问带循环的VBA - PB
EN

Stack Overflow用户
提问于 2016-07-01 17:24:25
回答 1查看 49关注 0票数 0

我编写了一个循环，它在工作表中选择值并将它们合并为一个。所有操作都很好，但是代码的最后一部分只适用于宏正在读取的最后一个工作表。请参阅下面的代码(为了便于理解，我缩小了大小，因此在irow = irow +1之间而不是只有37行，只有1或2行)：

代码语言：javascript
运行
复制
For Each Ws In ActiveWorkbook.Worksheets
If Ws.Range("B1") = "ON" Then
    Ws.Select

    If Sheets("GP").Cells(irow, 1).Offset(-1) <> Ws.Range("B9").Value Then
        Sheets("GP").Cells(irow, 1) = Ws.Range("B9").Value
    End If

    Sheets("GP").Cells(irow, 2) = "abc"

    irow = irow + 1

    If Sheets("GP").Cells(irow, 1).Offset(-1) <> Ws.Range("B9").Value Then
        Sheets("GP").Cells(irow, 1) = Ws.Range("B9").Value
    End If

    Sheets("GP").Cells(irow, 2) = "def"

    irow = irow + 1

    If Sheets("GP").Cells(irow, 1).Offset(-2) <> Ws.Range("B9").Value Then
        Sheets("GP").Cells(irow, 1) = Ws.Range("B9").Value
    End If

    Sheets("GP").Cells(irow, 2) = "ghi"

    irow = irow + 1

    If Sheets("GP").Cells(irow, 1).Offset(-3) <> Ws.Range("B9").Value Then
        Sheets("GP").Cells(irow, 1) = Ws.Range("B9").Value
    End If

    Sheets("GP").Cells(irow, 2) = "jkl"

    irow = irow + 1

    If Sheets("GP").Cells(irow, 1).Offset(-4) <> Ws.Range("B9").Value Then
        Sheets("GP").Cells(irow, 1) = Ws.Range("B9").Value
    End If

    Sheets("GP").Cells(irow, 2) = "mno"

    irow = irow + 1

    If Sheets("GP").Cells(irow, 1).Offset(-5) <> Ws.Range("B9").Value Then
        Sheets("GP").Cells(irow, 1) = Ws.Range("B9").Value
    End If

    Sheets("GP").Cells(irow, 2) = "pqr"

    irow = irow + 1

    If Sheets("GP").Cells(irow, 1).Offset(-6) <> Ws.Range("B9").Value Then
        Sheets("GP").Cells(irow, 1) = Ws.Range("B9").Value
    End If

    Sheets("GP").Cells(irow, 2) = "tuv"

    irow = irow + 1

    If Sheets("GP").Cells(irow, 1).Offset(-7) <> Ws.Range("B9").Value Then
        Sheets("GP").Cells(irow, 1) = Ws.Range("B9").Value
    End If

    Sheets("GP").Cells(irow, 2) = "wxy"

End If
Next

因此，在最后一个irow = irow +1之后的最后一部分只发生在循环从中选择值的最后一个工作表上。

如果您对整个代码感兴趣，请参见以下代码：

代码语言：javascript
运行
复制
Sub Generate_GP()

Application.ScreenUpdating = False
Application.AutoCorrect.AutoFillFormulasInLists = False

Dim Ws As Worksheet
Dim irow As String
Dim lastrow As String

irow = Range("B2000").End(xlUp).Row

For Each Ws In ActiveWorkbook.Worksheets
If Ws.Range("B1") = "ON" Then
    Ws.Select

    If Sheets("GP").Cells(irow, 1).Offset(-1) <> Ws.Range("B9").Value Then
        Sheets("GP").Cells(irow, 1) = Ws.Range("B9").Value
    End If

    Sheets("GP").Cells(irow, 2) = "P2 - Base Revenue"
    Sheets("GP").Cells(irow, 3) = "='" & Ws.Range("B9") & "'!$H$14"
    Sheets("GP").Cells(irow, 4) = "='" & Ws.Range("B9") & "'!$I$14"
    Sheets("GP").Cells(irow, 6) = "='" & Ws.Range("B9") & "'!$F$14"
    Sheets("GP").Cells(irow, 7) = "='" & Ws.Range("B9") & "'!$G$14"
    Sheets("GP").Cells(irow, 8) = "='" & Ws.Range("B9") & "'!$J$14"
    Sheets("GP").Cells(irow, 9) = "='" & Ws.Range("B9") & "'!$K$14"
    Sheets("GP").Cells(irow, 10) = "='" & Ws.Range("B9") & "'!$L$14"
    Sheets("GP").Cells(irow, 11) = "='" & Ws.Range("B9") & "'!$M$14"
    Sheets("GP").Cells(irow, 12) = "='" & Ws.Range("B9") & "'!$N$14"
    Sheets("GP").Cells(irow, 13) = "='" & Ws.Range("B9") & "'!$O$14"
    Sheets("GP").Cells(irow, 14) = "='" & Ws.Range("B9") & "'!$P$14"
    Sheets("GP").Cells(irow, 15) = "='" & Ws.Range("B9") & "'!$Q$14"
    Sheets("GP").Cells(irow, 16) = "='" & Ws.Range("B9") & "'!$R$14"
    Sheets("GP").Cells(irow, 17) = "='" & Ws.Range("B9") & "'!$S$14"
    Sheets("GP").Cells(irow, 18) = "='" & Ws.Range("B9") & "'!$T$14"
    Sheets("GP").Cells(irow, 19) = "='" & Ws.Range("B9") & "'!$U$14"
    Sheets("GP").Cells(irow, 22) = "='" & Ws.Range("B9") & "'!$W$14"
    Sheets("GP").Cells(irow, 23) = "='" & Ws.Range("B9") & "'!$X$14"
    Sheets("GP").Cells(irow, 24) = "='" & Ws.Range("B9") & "'!$Y$14"
    Sheets("GP").Cells(irow, 25) = "='" & Ws.Range("B9") & "'!$Z$14"
    Sheets("GP").Cells(irow, 26) = "='" & Ws.Range("B9") & "'!$AA$14"
    Sheets("GP").Cells(irow, 27) = "='" & Ws.Range("B9") & "'!$AB$14"
    Sheets("GP").Cells(irow, 28) = "='" & Ws.Range("B9") & "'!$AC$14"
    Sheets("GP").Cells(irow, 29) = "='" & Ws.Range("B9") & "'!$AD$14"
    Sheets("GP").Cells(irow, 30) = "='" & Ws.Range("B9") & "'!$AE$14"
    Sheets("GP").Cells(irow, 31) = "='" & Ws.Range("B9") & "'!$AF$14"
    Sheets("GP").Cells(irow, 32) = "='" & Ws.Range("B9") & "'!$AG$14"
    Sheets("GP").Cells(irow, 34) = "='" & Ws.Range("B9") & "'!$AH$14"
    Sheets("GP").Cells(irow, 35) = "='" & Ws.Range("B9") & "'!$AJ$14"
    Sheets("GP").Cells(irow, 36) = "='" & Ws.Range("B9") & "'!$AK$14"
    Sheets("GP").Cells(irow, 37) = "='" & Ws.Range("B9") & "'!$AL$14"
    Sheets("GP").Cells(irow, 36) = "='" & Ws.Range("B9") & "'!$AM$14"

    irow = irow + 1

    If Sheets("GP").Cells(irow, 1).Offset(-1) <> Ws.Range("B9").Value Then
        Sheets("GP").Cells(irow, 1) = Ws.Range("B9").Value
    End If

    Sheets("GP").Cells(irow, 2) = "P2 - Costs"
    Sheets("GP").Cells(irow, 3) = "='" & Ws.Range("B9") & "'!$H$15"
    Sheets("GP").Cells(irow, 4) = "='" & Ws.Range("B9") & "'!$I$15"
    Sheets("GP").Cells(irow, 6) = "='" & Ws.Range("B9") & "'!$F$15"
    Sheets("GP").Cells(irow, 7) = "='" & Ws.Range("B9") & "'!$G$15"
    Sheets("GP").Cells(irow, 8) = "='" & Ws.Range("B9") & "'!$J$15"
    Sheets("GP").Cells(irow, 9) = "='" & Ws.Range("B9") & "'!$K$15"
    Sheets("GP").Cells(irow, 10) = "='" & Ws.Range("B9") & "'!$L$15"
    Sheets("GP").Cells(irow, 11) = "='" & Ws.Range("B9") & "'!$M$15"
    Sheets("GP").Cells(irow, 12) = "='" & Ws.Range("B9") & "'!$N$15"
    Sheets("GP").Cells(irow, 13) = "='" & Ws.Range("B9") & "'!$O$15"
    Sheets("GP").Cells(irow, 14) = "='" & Ws.Range("B9") & "'!$P$15"
    Sheets("GP").Cells(irow, 15) = "='" & Ws.Range("B9") & "'!$Q$15"
    Sheets("GP").Cells(irow, 16) = "='" & Ws.Range("B9") & "'!$R$15"
    Sheets("GP").Cells(irow, 17) = "='" & Ws.Range("B9") & "'!$S$15"
    Sheets("GP").Cells(irow, 18) = "='" & Ws.Range("B9") & "'!$T$15"
    Sheets("GP").Cells(irow, 19) = "='" & Ws.Range("B9") & "'!$U$15"
    Sheets("GP").Cells(irow, 21) = "='" & Ws.Range("B9") & "'!$W$15"
    Sheets("GP").Cells(irow, 22) = "='" & Ws.Range("B9") & "'!$X$15"
    Sheets("GP").Cells(irow, 23) = "='" & Ws.Range("B9") & "'!$Y$15"
    Sheets("GP").Cells(irow, 24) = "='" & Ws.Range("B9") & "'!$Z$15"
    Sheets("GP").Cells(irow, 25) = "='" & Ws.Range("B9") & "'!$AA$15"
    Sheets("GP").Cells(irow, 26) = "='" & Ws.Range("B9") & "'!$AB$15"
    Sheets("GP").Cells(irow, 27) = "='" & Ws.Range("B9") & "'!$AC$15"
    Sheets("GP").Cells(irow, 28) = "='" & Ws.Range("B9") & "'!$AD$15"
    Sheets("GP").Cells(irow, 29) = "='" & Ws.Range("B9") & "'!$AE$15"
    Sheets("GP").Cells(irow, 30) = "='" & Ws.Range("B9") & "'!$AF$15"
    Sheets("GP").Cells(irow, 31) = "='" & Ws.Range("B9") & "'!$AG$15"
    Sheets("GP").Cells(irow, 32) = "='" & Ws.Range("B9") & "'!$AH$15"
    Sheets("GP").Cells(irow, 34) = "='" & Ws.Range("B9") & "'!$AJ$15"
    Sheets("GP").Cells(irow, 35) = "='" & Ws.Range("B9") & "'!$AK$15"
    Sheets("GP").Cells(irow, 36) = "='" & Ws.Range("B9") & "'!$AL$15"
    Sheets("GP").Cells(irow, 37) = "='" & Ws.Range("B9") & "'!$AM$15"

    irow = irow + 1

    If Sheets("GP").Cells(irow, 1).Offset(-2) <> Ws.Range("B9").Value Then
        Sheets("GP").Cells(irow, 1) = Ws.Range("B9").Value
    End If

    Sheets("GP").Cells(irow, 2) = "P2 - Base GP"
    Sheets("GP").Cells(irow, 3) = "='" & Ws.Range("B9") & "'!$H$16"
    Sheets("GP").Cells(irow, 4) = "='" & Ws.Range("B9") & "'!$I$16"
    Sheets("GP").Cells(irow, 6) = "='" & Ws.Range("B9") & "'!$F$16"
    Sheets("GP").Cells(irow, 7) = "='" & Ws.Range("B9") & "'!$G$16"
    Sheets("GP").Cells(irow, 8) = "='" & Ws.Range("B9") & "'!$J$16"
    Sheets("GP").Cells(irow, 9) = "='" & Ws.Range("B9") & "'!$K$16"
    Sheets("GP").Cells(irow, 10) = "='" & Ws.Range("B9") & "'!$L$16"
    Sheets("GP").Cells(irow, 11) = "='" & Ws.Range("B9") & "'!$M$16"
    Sheets("GP").Cells(irow, 12) = "='" & Ws.Range("B9") & "'!$N$16"
    Sheets("GP").Cells(irow, 13) = "='" & Ws.Range("B9") & "'!$O$16"
    Sheets("GP").Cells(irow, 14) = "='" & Ws.Range("B9") & "'!$P$16"
    Sheets("GP").Cells(irow, 15) = "='" & Ws.Range("B9") & "'!$Q$16"
    Sheets("GP").Cells(irow, 16) = "='" & Ws.Range("B9") & "'!$R$16"
    Sheets("GP").Cells(irow, 17) = "='" & Ws.Range("B9") & "'!$S$16"
    Sheets("GP").Cells(irow, 18) = "='" & Ws.Range("B9") & "'!$T$16"
    Sheets("GP").Cells(irow, 19) = "='" & Ws.Range("B9") & "'!$U$16"
    Sheets("GP").Cells(irow, 21) = "='" & Ws.Range("B9") & "'!$W$16"
    Sheets("GP").Cells(irow, 22) = "='" & Ws.Range("B9") & "'!$X$16"
    Sheets("GP").Cells(irow, 23) = "='" & Ws.Range("B9") & "'!$Y$16"
    Sheets("GP").Cells(irow, 24) = "='" & Ws.Range("B9") & "'!$Z$16"
    Sheets("GP").Cells(irow, 25) = "='" & Ws.Range("B9") & "'!$AA$16"
    Sheets("GP").Cells(irow, 26) = "='" & Ws.Range("B9") & "'!$AB$16"
    Sheets("GP").Cells(irow, 27) = "='" & Ws.Range("B9") & "'!$AC$16"
    Sheets("GP").Cells(irow, 28) = "='" & Ws.Range("B9") & "'!$AD$16"
    Sheets("GP").Cells(irow, 29) = "='" & Ws.Range("B9") & "'!$AE$16"
    Sheets("GP").Cells(irow, 30) = "='" & Ws.Range("B9") & "'!$AF$16"
    Sheets("GP").Cells(irow, 31) = "='" & Ws.Range("B9") & "'!$AG$16"
    Sheets("GP").Cells(irow, 32) = "='" & Ws.Range("B9") & "'!$AH$16"
    Sheets("GP").Cells(irow, 34) = "='" & Ws.Range("B9") & "'!$AJ$16"
    Sheets("GP").Cells(irow, 35) = "='" & Ws.Range("B9") & "'!$AK$16"
    Sheets("GP").Cells(irow, 36) = "='" & Ws.Range("B9") & "'!$AL$16"
    Sheets("GP").Cells(irow, 37) = "='" & Ws.Range("B9") & "'!$AM$16"

    irow = irow + 1

    If Sheets("GP").Cells(irow, 1).Offset(-3) <> Ws.Range("B9").Value Then
        Sheets("GP").Cells(irow, 1) = Ws.Range("B9").Value
    End If

    'B - P2 - Base Revenue
    Sheets("GP").Cells(irow, 2) = "%GP"

    Sheets("GP").Cells(irow, 3) = "=" & Sheets("GP").Cells(irow - 1, 3).Address & "/" & Sheets("GP").Cells(irow - 3, 3).Address & ""
    Sheets("GP").Cells(irow, 4) = "=" & Sheets("GP").Cells(irow - 1, 4).Address & "/" & Sheets("GP").Cells(irow - 3, 4).Address & ""
    Sheets("GP").Cells(irow, 6) = "=" & Sheets("GP").Cells(irow - 1, 6).Address & "/" & Sheets("GP").Cells(irow - 3, 6).Address & ""
    Sheets("GP").Cells(irow, 7) = "=" & Sheets("GP").Cells(irow - 1, 7).Address & "/" & Sheets("GP").Cells(irow - 3, 7).Address & ""
    Sheets("GP").Cells(irow, 8) = "=" & Sheets("GP").Cells(irow - 1, 8).Address & "/" & Sheets("GP").Cells(irow - 3, 8).Address & ""
    Sheets("GP").Cells(irow, 9) = "=" & Sheets("GP").Cells(irow - 1, 9).Address & "/" & Sheets("GP").Cells(irow - 3, 9).Address & ""
    Sheets("GP").Cells(irow, 10) = "=" & Sheets("GP").Cells(irow - 1, 10).Address & "/" & Sheets("GP").Cells(irow - 3, 10).Address & ""
    Sheets("GP").Cells(irow, 11) = "=" & Sheets("GP").Cells(irow - 1, 11).Address & "/" & Sheets("GP").Cells(irow - 3, 11).Address & ""
    Sheets("GP").Cells(irow, 12) = "=" & Sheets("GP").Cells(irow - 1, 12).Address & "/" & Sheets("GP").Cells(irow - 3, 12).Address & ""
    Sheets("GP").Cells(irow, 13) = "=" & Sheets("GP").Cells(irow - 1, 13).Address & "/" & Sheets("GP").Cells(irow - 3, 13).Address & ""
    Sheets("GP").Cells(irow, 14) = "=" & Sheets("GP").Cells(irow - 1, 14).Address & "/" & Sheets("GP").Cells(irow - 3, 14).Address & ""
    Sheets("GP").Cells(irow, 15) = "=" & Sheets("GP").Cells(irow - 1, 15).Address & "/" & Sheets("GP").Cells(irow - 3, 15).Address & ""
    Sheets("GP").Cells(irow, 16) = "=" & Sheets("GP").Cells(irow - 1, 16).Address & "/" & Sheets("GP").Cells(irow - 3, 16).Address & ""
    Sheets("GP").Cells(irow, 17) = "=" & Sheets("GP").Cells(irow - 1, 17).Address & "/" & Sheets("GP").Cells(irow - 3, 17).Address & ""
    Sheets("GP").Cells(irow, 18) = "=" & Sheets("GP").Cells(irow - 1, 18).Address & "/" & Sheets("GP").Cells(irow - 3, 18).Address & ""
    Sheets("GP").Cells(irow, 19) = "=" & Sheets("GP").Cells(irow - 1, 19).Address & "/" & Sheets("GP").Cells(irow - 3, 19).Address & ""
    Sheets("GP").Cells(irow, 21) = "=" & Sheets("GP").Cells(irow - 1, 21).Address & "/" & Sheets("GP").Cells(irow - 3, 21).Address & ""
    Sheets("GP").Cells(irow, 22) = "=" & Sheets("GP").Cells(irow - 1, 22).Address & "/" & Sheets("GP").Cells(irow - 3, 22).Address & ""
    Sheets("GP").Cells(irow, 23) = "=" & Sheets("GP").Cells(irow - 1, 23).Address & "/" & Sheets("GP").Cells(irow - 3, 23).Address & ""
    Sheets("GP").Cells(irow, 24) = "=" & Sheets("GP").Cells(irow - 1, 24).Address & "/" & Sheets("GP").Cells(irow - 3, 24).Address & ""
    Sheets("GP").Cells(irow, 25) = "=" & Sheets("GP").Cells(irow - 1, 25).Address & "/" & Sheets("GP").Cells(irow - 3, 25).Address & ""
    Sheets("GP").Cells(irow, 26) = "=" & Sheets("GP").Cells(irow - 1, 26).Address & "/" & Sheets("GP").Cells(irow - 3, 26).Address & ""
    Sheets("GP").Cells(irow, 27) = "=" & Sheets("GP").Cells(irow - 1, 27).Address & "/" & Sheets("GP").Cells(irow - 3, 27).Address & ""
    Sheets("GP").Cells(irow, 28) = "=" & Sheets("GP").Cells(irow - 1, 28).Address & "/" & Sheets("GP").Cells(irow - 3, 28).Address & ""
    Sheets("GP").Cells(irow, 29) = "=" & Sheets("GP").Cells(irow - 1, 29).Address & "/" & Sheets("GP").Cells(irow - 3, 29).Address & ""
    Sheets("GP").Cells(irow, 30) = "=" & Sheets("GP").Cells(irow - 1, 30).Address & "/" & Sheets("GP").Cells(irow - 3, 30).Address & ""
    Sheets("GP").Cells(irow, 31) = "=" & Sheets("GP").Cells(irow - 1, 31).Address & "/" & Sheets("GP").Cells(irow - 3, 31).Address & ""
    Sheets("GP").Cells(irow, 32) = "=" & Sheets("GP").Cells(irow - 1, 32).Address & "/" & Sheets("GP").Cells(irow - 3, 32).Address & ""
    Sheets("GP").Cells(irow, 34) = "=" & Sheets("GP").Cells(irow - 1, 34).Address & "/" & Sheets("GP").Cells(irow - 3, 34).Address & ""
    Sheets("GP").Cells(irow, 35) = "=" & Sheets("GP").Cells(irow - 1, 35).Address & "/" & Sheets("GP").Cells(irow - 3, 35).Address & ""
    Sheets("GP").Cells(irow, 36) = "=" & Sheets("GP").Cells(irow - 1, 36).Address & "/" & Sheets("GP").Cells(irow - 3, 36).Address & ""
    Sheets("GP").Cells(irow, 37) = "=" & Sheets("GP").Cells(irow - 1, 37).Address & "/" & Sheets("GP").Cells(irow - 3, 37).Address & ""

    irow = irow + 1

    If Sheets("GP").Cells(irow, 1).Offset(-4) <> Ws.Range("B9").Value Then
        Sheets("GP").Cells(irow, 1) = Ws.Range("B9").Value
    End If

    Sheets("GP").Cells(irow, 2) = "P5 - Variable Revenue"
    Sheets("GP").Cells(irow, 3) = "='" & Ws.Range("B9") & "'!$H$18"
    Sheets("GP").Cells(irow, 4) = "='" & Ws.Range("B9") & "'!$I$18"
    Sheets("GP").Cells(irow, 6) = "='" & Ws.Range("B9") & "'!$F$18"
    Sheets("GP").Cells(irow, 7) = "='" & Ws.Range("B9") & "'!$G$18"
    Sheets("GP").Cells(irow, 8) = "='" & Ws.Range("B9") & "'!$J$18"
    Sheets("GP").Cells(irow, 9) = "='" & Ws.Range("B9") & "'!$K$18"
    Sheets("GP").Cells(irow, 10) = "='" & Ws.Range("B9") & "'!$L$18"
    Sheets("GP").Cells(irow, 11) = "='" & Ws.Range("B9") & "'!$M$18"
    Sheets("GP").Cells(irow, 12) = "='" & Ws.Range("B9") & "'!$N$18"
    Sheets("GP").Cells(irow, 13) = "='" & Ws.Range("B9") & "'!$O$18"
    Sheets("GP").Cells(irow, 14) = "='" & Ws.Range("B9") & "'!$P$18"
    Sheets("GP").Cells(irow, 15) = "='" & Ws.Range("B9") & "'!$Q$18"
    Sheets("GP").Cells(irow, 16) = "='" & Ws.Range("B9") & "'!$R$18"
    Sheets("GP").Cells(irow, 17) = "='" & Ws.Range("B9") & "'!$S$18"
    Sheets("GP").Cells(irow, 18) = "='" & Ws.Range("B9") & "'!$T$18"
    Sheets("GP").Cells(irow, 19) = "='" & Ws.Range("B9") & "'!$U$18"
    Sheets("GP").Cells(irow, 21) = "='" & Ws.Range("B9") & "'!$W$18"
    Sheets("GP").Cells(irow, 22) = "='" & Ws.Range("B9") & "'!$X$18"
    Sheets("GP").Cells(irow, 23) = "='" & Ws.Range("B9") & "'!$Y$18"
    Sheets("GP").Cells(irow, 24) = "='" & Ws.Range("B9") & "'!$Z$18"
    Sheets("GP").Cells(irow, 25) = "='" & Ws.Range("B9") & "'!$AA$18"
    Sheets("GP").Cells(irow, 26) = "='" & Ws.Range("B9") & "'!$AB$18"
    Sheets("GP").Cells(irow, 27) = "='" & Ws.Range("B9") & "'!$AC$18"
    Sheets("GP").Cells(irow, 28) = "='" & Ws.Range("B9") & "'!$AD$18"
    Sheets("GP").Cells(irow, 29) = "='" & Ws.Range("B9") & "'!$AE$18"
    Sheets("GP").Cells(irow, 30) = "='" & Ws.Range("B9") & "'!$AF$18"
    Sheets("GP").Cells(irow, 31) = "='" & Ws.Range("B9") & "'!$AG$18"
    Sheets("GP").Cells(irow, 32) = "='" & Ws.Range("B9") & "'!$AH$18"
    Sheets("GP").Cells(irow, 34) = "='" & Ws.Range("B9") & "'!$AJ$18"
    Sheets("GP").Cells(irow, 35) = "='" & Ws.Range("B9") & "'!$AK$18"
    Sheets("GP").Cells(irow, 36) = "='" & Ws.Range("B9") & "'!$AL$18"
    Sheets("GP").Cells(irow, 37) = "='" & Ws.Range("B9") & "'!$AM$18"

    irow = irow + 1

    If Sheets("GP").Cells(irow, 1).Offset(-5) <> Ws.Range("B9").Value Then
        Sheets("GP").Cells(irow, 1) = Ws.Range("B9").Value
    End If

    Sheets("GP").Cells(irow, 2) = "P5 - Costs"
    Sheets("GP").Cells(irow, 3) = "='" & Ws.Range("B9") & "'!$H$19"
    Sheets("GP").Cells(irow, 4) = "='" & Ws.Range("B9") & "'!$I$19"
    Sheets("GP").Cells(irow, 6) = "='" & Ws.Range("B9") & "'!$F$19"
    Sheets("GP").Cells(irow, 7) = "='" & Ws.Range("B9") & "'!$G$19"
    Sheets("GP").Cells(irow, 8) = "='" & Ws.Range("B9") & "'!$J$19"
    Sheets("GP").Cells(irow, 9) = "='" & Ws.Range("B9") & "'!$K$19"
    Sheets("GP").Cells(irow, 10) = "='" & Ws.Range("B9") & "'!$L$19"
    Sheets("GP").Cells(irow, 11) = "='" & Ws.Range("B9") & "'!$M$19"
    Sheets("GP").Cells(irow, 12) = "='" & Ws.Range("B9") & "'!$N$19"
    Sheets("GP").Cells(irow, 13) = "='" & Ws.Range("B9") & "'!$O$19"
    Sheets("GP").Cells(irow, 14) = "='" & Ws.Range("B9") & "'!$P$19"
    Sheets("GP").Cells(irow, 15) = "='" & Ws.Range("B9") & "'!$Q$19"
    Sheets("GP").Cells(irow, 16) = "='" & Ws.Range("B9") & "'!$R$19"
    Sheets("GP").Cells(irow, 17) = "='" & Ws.Range("B9") & "'!$S$19"
    Sheets("GP").Cells(irow, 18) = "='" & Ws.Range("B9") & "'!$T$19"
    Sheets("GP").Cells(irow, 19) = "='" & Ws.Range("B9") & "'!$U$19"
    Sheets("GP").Cells(irow, 21) = "='" & Ws.Range("B9") & "'!$W$19"
    Sheets("GP").Cells(irow, 22) = "='" & Ws.Range("B9") & "'!$X$19"
    Sheets("GP").Cells(irow, 23) = "='" & Ws.Range("B9") & "'!$Y$19"
    Sheets("GP").Cells(irow, 24) = "='" & Ws.Range("B9") & "'!$Z$19"
    Sheets("GP").Cells(irow, 25) = "='" & Ws.Range("B9") & "'!$AA$19"
    Sheets("GP").Cells(irow, 26) = "='" & Ws.Range("B9") & "'!$AB$19"
    Sheets("GP").Cells(irow, 27) = "='" & Ws.Range("B9") & "'!$AC$19"
    Sheets("GP").Cells(irow, 28) = "='" & Ws.Range("B9") & "'!$AD$19"
    Sheets("GP").Cells(irow, 29) = "='" & Ws.Range("B9") & "'!$AE$19"
    Sheets("GP").Cells(irow, 30) = "='" & Ws.Range("B9") & "'!$AF$19"
    Sheets("GP").Cells(irow, 31) = "='" & Ws.Range("B9") & "'!$AG$19"
    Sheets("GP").Cells(irow, 32) = "='" & Ws.Range("B9") & "'!$AH$19"
    Sheets("GP").Cells(irow, 34) = "='" & Ws.Range("B9") & "'!$AJ$19"
    Sheets("GP").Cells(irow, 35) = "='" & Ws.Range("B9") & "'!$AK$19"
    Sheets("GP").Cells(irow, 36) = "='" & Ws.Range("B9") & "'!$AL$19"
    Sheets("GP").Cells(irow, 37) = "='" & Ws.Range("B9") & "'!$AM$19"

    irow = irow + 1

    If Sheets("GP").Cells(irow, 1).Offset(-6) <> Ws.Range("B9").Value Then
        Sheets("GP").Cells(irow, 1) = Ws.Range("B9").Value
    End If

    Sheets("GP").Cells(irow, 2) = "P5 - Variable GP"
    Sheets("GP").Cells(irow, 3) = "='" & Ws.Range("B9") & "'!$H$20"
    Sheets("GP").Cells(irow, 4) = "='" & Ws.Range("B9") & "'!$I$20"
    Sheets("GP").Cells(irow, 6) = "='" & Ws.Range("B9") & "'!$F$20"
    Sheets("GP").Cells(irow, 7) = "='" & Ws.Range("B9") & "'!$G$20"
    Sheets("GP").Cells(irow, 8) = "='" & Ws.Range("B9") & "'!$J$20"
    Sheets("GP").Cells(irow, 9) = "='" & Ws.Range("B9") & "'!$K$20"
    Sheets("GP").Cells(irow, 10) = "='" & Ws.Range("B9") & "'!$L$20"
    Sheets("GP").Cells(irow, 11) = "='" & Ws.Range("B9") & "'!$M$20"
    Sheets("GP").Cells(irow, 12) = "='" & Ws.Range("B9") & "'!$N$20"
    Sheets("GP").Cells(irow, 13) = "='" & Ws.Range("B9") & "'!$O$20"
    Sheets("GP").Cells(irow, 14) = "='" & Ws.Range("B9") & "'!$P$20"
    Sheets("GP").Cells(irow, 15) = "='" & Ws.Range("B9") & "'!$Q$20"
    Sheets("GP").Cells(irow, 16) = "='" & Ws.Range("B9") & "'!$R$20"
    Sheets("GP").Cells(irow, 17) = "='" & Ws.Range("B9") & "'!$S$20"
    Sheets("GP").Cells(irow, 18) = "='" & Ws.Range("B9") & "'!$T$20"
    Sheets("GP").Cells(irow, 19) = "='" & Ws.Range("B9") & "'!$U$20"
    Sheets("GP").Cells(irow, 21) = "='" & Ws.Range("B9") & "'!$W$20"
    Sheets("GP").Cells(irow, 22) = "='" & Ws.Range("B9") & "'!$X$20"
    Sheets("GP").Cells(irow, 23) = "='" & Ws.Range("B9") & "'!$Y$20"
    Sheets("GP").Cells(irow, 24) = "='" & Ws.Range("B9") & "'!$Z$20"
    Sheets("GP").Cells(irow, 25) = "='" & Ws.Range("B9") & "'!$AA$20"
    Sheets("GP").Cells(irow, 26) = "='" & Ws.Range("B9") & "'!$AB$20"
    Sheets("GP").Cells(irow, 27) = "='" & Ws.Range("B9") & "'!$AC$20"
    Sheets("GP").Cells(irow, 28) = "='" & Ws.Range("B9") & "'!$AD$20"
    Sheets("GP").Cells(irow, 29) = "='" & Ws.Range("B9") & "'!$AE$20"
    Sheets("GP").Cells(irow, 30) = "='" & Ws.Range("B9") & "'!$AF$20"
    Sheets("GP").Cells(irow, 31) = "='" & Ws.Range("B9") & "'!$AG$20"
    Sheets("GP").Cells(irow, 32) = "='" & Ws.Range("B9") & "'!$AH$20"
    Sheets("GP").Cells(irow, 34) = "='" & Ws.Range("B9") & "'!$AJ$20"
    Sheets("GP").Cells(irow, 35) = "='" & Ws.Range("B9") & "'!$AK$20"
    Sheets("GP").Cells(irow, 36) = "='" & Ws.Range("B9") & "'!$AL$20"
    Sheets("GP").Cells(irow, 37) = "='" & Ws.Range("B9") & "'!$AM$20"

    irow = irow + 1

    If Sheets("GP").Cells(irow, 1).Offset(-7) <> Ws.Range("B9").Value Then
        Sheets("GP").Cells(irow, 1) = Ws.Range("B9").Value
    End If


    Sheets("GP").Cells(irow, 2) = "%GP"
    Sheets("GP").Cells(irow, 3) = "=" & Sheets("GP").Cells(irow - 1, 3).Address & "/" & Sheets("GP").Cells(irow - 3, 3).Address & ""
    Sheets("GP").Cells(irow, 4) = "=" & Sheets("GP").Cells(irow - 1, 4).Address & "/" & Sheets("GP").Cells(irow - 3, 4).Address & ""
    Sheets("GP").Cells(irow, 6) = "=" & Sheets("GP").Cells(irow - 1, 6).Address & "/" & Sheets("GP").Cells(irow - 3, 6).Address & ""
    Sheets("GP").Cells(irow, 7) = "=" & Sheets("GP").Cells(irow - 1, 7).Address & "/" & Sheets("GP").Cells(irow - 3, 7).Address & ""
    Sheets("GP").Cells(irow, 8) = "=" & Sheets("GP").Cells(irow - 1, 8).Address & "/" & Sheets("GP").Cells(irow - 3, 8).Address & ""
    Sheets("GP").Cells(irow, 9) = "=" & Sheets("GP").Cells(irow - 1, 9).Address & "/" & Sheets("GP").Cells(irow - 3, 9).Address & ""
    Sheets("GP").Cells(irow, 10) = "=" & Sheets("GP").Cells(irow - 1, 10).Address & "/" & Sheets("GP").Cells(irow - 3, 10).Address & ""
    Sheets("GP").Cells(irow, 11) = "=" & Sheets("GP").Cells(irow - 1, 11).Address & "/" & Sheets("GP").Cells(irow - 3, 11).Address & ""
    Sheets("GP").Cells(irow, 12) = "=" & Sheets("GP").Cells(irow - 1, 12).Address & "/" & Sheets("GP").Cells(irow - 3, 12).Address & ""
    Sheets("GP").Cells(irow, 13) = "=" & Sheets("GP").Cells(irow - 1, 13).Address & "/" & Sheets("GP").Cells(irow - 3, 13).Address & ""
    Sheets("GP").Cells(irow, 14) = "=" & Sheets("GP").Cells(irow - 1, 14).Address & "/" & Sheets("GP").Cells(irow - 3, 14).Address & ""
    Sheets("GP").Cells(irow, 15) = "=" & Sheets("GP").Cells(irow - 1, 15).Address & "/" & Sheets("GP").Cells(irow - 3, 15).Address & ""
    Sheets("GP").Cells(irow, 16) = "=" & Sheets("GP").Cells(irow - 1, 16).Address & "/" & Sheets("GP").Cells(irow - 3, 16).Address & ""
    Sheets("GP").Cells(irow, 17) = "=" & Sheets("GP").Cells(irow - 1, 17).Address & "/" & Sheets("GP").Cells(irow - 3, 17).Address & ""
    Sheets("GP").Cells(irow, 18) = "=" & Sheets("GP").Cells(irow - 1, 18).Address & "/" & Sheets("GP").Cells(irow - 3, 18).Address & ""
    Sheets("GP").Cells(irow, 19) = "=" & Sheets("GP").Cells(irow - 1, 19).Address & "/" & Sheets("GP").Cells(irow - 3, 19).Address & ""
    Sheets("GP").Cells(irow, 21) = "=" & Sheets("GP").Cells(irow - 1, 21).Address & "/" & Sheets("GP").Cells(irow - 3, 21).Address & ""
    Sheets("GP").Cells(irow, 22) = "=" & Sheets("GP").Cells(irow - 1, 22).Address & "/" & Sheets("GP").Cells(irow - 3, 22).Address & ""
    Sheets("GP").Cells(irow, 23) = "=" & Sheets("GP").Cells(irow - 1, 23).Address & "/" & Sheets("GP").Cells(irow - 3, 23).Address & ""
    Sheets("GP").Cells(irow, 24) = "=" & Sheets("GP").Cells(irow - 1, 24).Address & "/" & Sheets("GP").Cells(irow - 3, 24).Address & ""
    Sheets("GP").Cells(irow, 25) = "=" & Sheets("GP").Cells(irow - 1, 25).Address & "/" & Sheets("GP").Cells(irow - 3, 25).Address & ""
    Sheets("GP").Cells(irow, 26) = "=" & Sheets("GP").Cells(irow - 1, 26).Address & "/" & Sheets("GP").Cells(irow - 3, 26).Address & ""
    Sheets("GP").Cells(irow, 27) = "=" & Sheets("GP").Cells(irow - 1, 27).Address & "/" & Sheets("GP").Cells(irow - 3, 27).Address & ""
    Sheets("GP").Cells(irow, 28) = "=" & Sheets("GP").Cells(irow - 1, 28).Address & "/" & Sheets("GP").Cells(irow - 3, 28).Address & ""
    Sheets("GP").Cells(irow, 29) = "=" & Sheets("GP").Cells(irow - 1, 29).Address & "/" & Sheets("GP").Cells(irow - 3, 29).Address & ""
    Sheets("GP").Cells(irow, 30) = "=" & Sheets("GP").Cells(irow - 1, 30).Address & "/" & Sheets("GP").Cells(irow - 3, 30).Address & ""
    Sheets("GP").Cells(irow, 31) = "=" & Sheets("GP").Cells(irow - 1, 31).Address & "/" & Sheets("GP").Cells(irow - 3, 31).Address & ""
    Sheets("GP").Cells(irow, 32) = "=" & Sheets("GP").Cells(irow - 1, 32).Address & "/" & Sheets("GP").Cells(irow - 3, 32).Address & ""
    Sheets("GP").Cells(irow, 34) = "=" & Sheets("GP").Cells(irow - 1, 34).Address & "/" & Sheets("GP").Cells(irow - 3, 34).Address & ""
    Sheets("GP").Cells(irow, 35) = "=" & Sheets("GP").Cells(irow - 1, 35).Address & "/" & Sheets("GP").Cells(irow - 3, 35).Address & ""
    Sheets("GP").Cells(irow, 36) = "=" & Sheets("GP").Cells(irow - 1, 36).Address & "/" & Sheets("GP").Cells(irow - 3, 36).Address & ""
    Sheets("GP").Cells(irow, 37) = "=" & Sheets("GP").Cells(irow - 1, 37).Address & "/" & Sheets("GP").Cells(irow - 3, 37).Address & ""

End If
Next

Sheets("GP").Activate
Application.ScreenUpdating = True

End Sub
vba
loops
row
EN

回答 1

Stack Overflow用户

回答已采纳

发布于 2016-07-01 17:46:20

实际上，它适用于所有工作表，但对于除最后一个之外的所有工作表，您都要覆盖它，因为在最后一个命令之后(或在第一个命令之前)不增加irow。

编辑:更多细节：irow指向最后一个非空行。在编写之前，您需要增加irow，以便它指向第一个空行，否则最后一行将被覆盖。在循环开始时，您不会增加irow，所以当循环开始时，最后一行中的内容都会被覆盖。因此，您需要在循环开始时添加irow = irow + 1。(编写完后，您还可以将irow点放在第一个空行和增量处，这是基于您如何初始化irow的)

您的代码可能会大大缩短，一旦它工作，您应该前往codereview。我现在在手机上，所以我不会再谈这个了。

附带注意：irow和lastrow是行号，那么为什么要使用字符串呢?将它们改为Long。(Integer在VBA中只有2个字节，所以使用Long，它是4个字节)。

使用字符串作为数字尤其危险，因为+运算符连接字符串(如&)运算符。因此，在执行irow = irow + 1时，您很幸运，VBA将字符串转换为数字数据类型，然后执行加法，然后将其转换回来，而不是将1转换为String并将其附加到irow字符串。

试试MsgBox "irow+irow=" & irow + irow或类似的东西，看看会发生什么;)

票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：

https://stackoverflow.com/questions/38150941

复制
相关文章

相似问题

领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2025 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 深公网安备号 44030502008569

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号 | 京公网安备号11010802020287

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档