代码简化

王跃峰

Sub 各种单耗()
Dim i&, x&
i = Sheets.Count '计算工作表数
For x = 2 To i '建立每个表循环
        With Sheets(x)
                .Range("C30").Formula = "=C28+'" & Sheets(x - 1).Name & "'!C30"
                .Range("C32").Formula = "=C30+'" & Sheets(x - 1).Name & "'!C32"
                .Range("C36").Formula = "=C34+'" & Sheets(x - 1).Name & "'!C36"
                .Range("C38").Formula = "=C36+'" & Sheets(x - 1).Name & "'!C38"
                .Range("C42").Formula = "=C40+'" & Sheets(x - 1).Name & "'!C42"
                .Range("C44").Formula = "=C42+'" & Sheets(x - 1).Name & "'!C44"
                .Range("C42").Formula = "=C40+'" & Sheets(x - 1).Name & "'!C42"
                .Range("C44").Formula = "=C42+'" & Sheets(x - 1).Name & "'!C44"
                .Range("C48").Formula = "=C46+'" & Sheets(x - 1).Name & "'!C48"
                .Range("C50").Formula = "=C48+'" & Sheets(x - 1).Name & "'!C50"
                .Range("D30").Formula = "=D28+'" & Sheets(x - 1).Name & "'!D30"
                .Range("D32").Formula = "=D30+'" & Sheets(x - 1).Name & "'!D32"
                .Range("D36").Formula = "=D34+'" & Sheets(x - 1).Name & "'!D36"
                .Range("D38").Formula = "=D36+'" & Sheets(x - 1).Name & "'!D38"
                .Range("D42").Formula = "=D40+'" & Sheets(x - 1).Name & "'!D42"
                .Range("D44").Formula = "=D42+'" & Sheets(x - 1).Name & "'!D44"
                .Range("D42").Formula = "=D40+'" & Sheets(x - 1).Name & "'!D42"
                .Range("D44").Formula = "=D42+'" & Sheets(x - 1).Name & "'!D44"
                .Range("D48").Formula = "=D46+'" & Sheets(x - 1).Name & "'!D48"
                .Range("D50").Formula = "=D48+'" & Sheets(x - 1).Name & "'!D50"
                .Range("E30").Formula = "=E28+'" & Sheets(x - 1).Name & "'!E30"
                .Range("E32").Formula = "=E30+'" & Sheets(x - 1).Name & "'!E32"
                .Range("E36").Formula = "=E34+'" & Sheets(x - 1).Name & "'!E36"
                .Range("E38").Formula = "=E36+'" & Sheets(x - 1).Name & "'!E38"
                .Range("E42").Formula = "=E40+'" & Sheets(x - 1).Name & "'!E42"
                .Range("E44").Formula = "=E42+'" & Sheets(x - 1).Name & "'!E44"
                .Range("E42").Formula = "=E40+'" & Sheets(x - 1).Name & "'!E42"
                .Range("E44").Formula = "=E42+'" & Sheets(x - 1).Name & "'!E44"
                .Range("E48").Formula = "=E46+'" & Sheets(x - 1).Name & "'!E48"
                .Range("E50").Formula = "=E48+'" & Sheets(x - 1).Name & "'!E50"
                .Range("F30").Formula = "=F28+'" & Sheets(x - 1).Name & "'!F30"
                .Range("F32").Formula = "=F30+'" & Sheets(x - 1).Name & "'!F32"
                .Range("F36").Formula = "=F34+'" & Sheets(x - 1).Name & "'!F36"
                .Range("F38").Formula = "=F36+'" & Sheets(x - 1).Name & "'!F38"
                .Range("F42").Formula = "=F40+'" & Sheets(x - 1).Name & "'!F42"
                .Range("F44").Formula = "=F42+'" & Sheets(x - 1).Name & "'!F44"
                .Range("F42").Formula = "=F40+'" & Sheets(x - 1).Name & "'!F42"
                .Range("F44").Formula = "=F42+'" & Sheets(x - 1).Name & "'!F44"
                .Range("F48").Formula = "=F46+'" & Sheets(x - 1).Name & "'!F48"
                .Range("F50").Formula = "=F48+'" & Sheets(x - 1).Name & "'!F50"
                .Range("G30").Formula = "=G28+'" & Sheets(x - 1).Name & "'!G30"
                .Range("G32").Formula = "=G30+'" & Sheets(x - 1).Name & "'!G32"
                .Range("G36").Formula = "=G34+'" & Sheets(x - 1).Name & "'!G36"
                .Range("G38").Formula = "=G36+'" & Sheets(x - 1).Name & "'!G38"
                .Range("G42").Formula = "=G40+'" & Sheets(x - 1).Name & "'!G42"
                .Range("G44").Formula = "=G42+'" & Sheets(x - 1).Name & "'!G44"
                .Range("G42").Formula = "=G40+'" & Sheets(x - 1).Name & "'!G42"
                .Range("G44").Formula = "=G42+'" & Sheets(x - 1).Name & "'!G44"
                .Range("G48").Formula = "=G46+'" & Sheets(x - 1).Name & "'!G48"
                .Range("G50").Formula = "=G48+'" & Sheets(x - 1).Name & "'!G50"
                .Range("H30").Formula = "=H28+'" & Sheets(x - 1).Name & "'!H30"
                .Range("H32").Formula = "=H30+'" & Sheets(x - 1).Name & "'!H32"
                .Range("H36").Formula = "=H34+'" & Sheets(x - 1).Name & "'!H36"
                .Range("H38").Formula = "=H36+'" & Sheets(x - 1).Name & "'!H38"
                .Range("H42").Formula = "=H40+'" & Sheets(x - 1).Name & "'!H42"
                .Range("H44").Formula = "=H42+'" & Sheets(x - 1).Name & "'!H44"
                .Range("H42").Formula = "=H40+'" & Sheets(x - 1).Name & "'!H42"
                .Range("H44").Formula = "=H42+'" & Sheets(x - 1).Name & "'!H44"
                .Range("H48").Formula = "=H46+'" & Sheets(x - 1).Name & "'!H48"
                .Range("H50").Formula = "=H48+'" & Sheets(x - 1).Name & "'!H50"
                .Range("I30").Formula = "=I28+'" & Sheets(x - 1).Name & "'!I30"
                .Range("I32").Formula = "=I30+'" & Sheets(x - 1).Name & "'!I32"
                .Range("I36").Formula = "=I34+'" & Sheets(x - 1).Name & "'!I36"
                .Range("I38").Formula = "=I36+'" & Sheets(x - 1).Name & "'!I38"
                .Range("I42").Formula = "=I40+'" & Sheets(x - 1).Name & "'!I42"
                .Range("I44").Formula = "=I42+'" & Sheets(x - 1).Name & "'!I44"
                .Range("I42").Formula = "=I40+'" & Sheets(x - 1).Name & "'!I42"
                .Range("I44").Formula = "=I42+'" & Sheets(x - 1).Name & "'!I44"
                .Range("I48").Formula = "=I46+'" & Sheets(x - 1).Name & "'!I48"
                .Range("I50").Formula = "=I48+'" & Sheets(x - 1).Name & "'!I50"
                .Range("J30").Formula = "=J28+'" & Sheets(x - 1).Name & "'!J30"
                .Range("J32").Formula = "=J30+'" & Sheets(x - 1).Name & "'!J32"
                .Range("J36").Formula = "=J34+'" & Sheets(x - 1).Name & "'!J36"
                .Range("J38").Formula = "=J36+'" & Sheets(x - 1).Name & "'!J38"
                .Range("J42").Formula = "=J40+'" & Sheets(x - 1).Name & "'!J42"
                .Range("J44").Formula = "=J42+'" & Sheets(x - 1).Name & "'!J44"
                .Range("J42").Formula = "=J40+'" & Sheets(x - 1).Name & "'!J42"
                .Range("J44").Formula = "=J42+'" & Sheets(x - 1).Name & "'!J44"
                .Range("J48").Formula = "=J46+'" & Sheets(x - 1).Name & "'!J48"
                .Range("J50").Formula = "=J48+'" & Sheets(x - 1).Name & "'!J50"
                .Range("K30").Formula = "=K28+'" & Sheets(x - 1).Name & "'!K30"
                .Range("K32").Formula = "=K30+'" & Sheets(x - 1).Name & "'!K32"
                .Range("K36").Formula = "=K34+'" & Sheets(x - 1).Name & "'!K36"
                .Range("K38").Formula = "=K36+'" & Sheets(x - 1).Name & "'!K38"
                .Range("K42").Formula = "=K40+'" & Sheets(x - 1).Name & "'!K42"
                .Range("K44").Formula = "=K42+'" & Sheets(x - 1).Name & "'!K44"
                .Range("K42").Formula = "=K40+'" & Sheets(x - 1).Name & "'!K42"
                .Range("K44").Formula = "=K42+'" & Sheets(x - 1).Name & "'!K44"
                .Range("K48").Formula = "=K46+'" & Sheets(x - 1).Name & "'!K48"
                .Range("K50").Formula = "=K48+'" & Sheets(x - 1).Name & "'!K50"
                .Range("L30").Formula = "=L28+'" & Sheets(x - 1).Name & "'!L30"
                .Range("L32").Formula = "=L30+'" & Sheets(x - 1).Name & "'!L32"
                .Range("L36").Formula = "=L34+'" & Sheets(x - 1).Name & "'!L36"
                .Range("L38").Formula = "=L36+'" & Sheets(x - 1).Name & "'!L38"
                .Range("L42").Formula = "=L40+'" & Sheets(x - 1).Name & "'!L42"
                .Range("L44").Formula = "=L42+'" & Sheets(x - 1).Name & "'!L44"
                .Range("L42").Formula = "=L40+'" & Sheets(x - 1).Name & "'!L42"
                .Range("L44").Formula = "=L42+'" & Sheets(x - 1).Name & "'!L44"
                .Range("L48").Formula = "=L46+'" & Sheets(x - 1).Name & "'!L48"
                .Range("L50").Formula = "=L48+'" & Sheets(x - 1).Name & "'!L50"
                .Range("M30").Formula = "=M28+'" & Sheets(x - 1).Name & "'!M30"
                .Range("M32").Formula = "=M30+'" & Sheets(x - 1).Name & "'!M32"
                .Range("M36").Formula = "=M34+'" & Sheets(x - 1).Name & "'!M36"
                .Range("M38").Formula = "=M36+'" & Sheets(x - 1).Name & "'!M38"
                .Range("M42").Formula = "=M40+'" & Sheets(x - 1).Name & "'!M42"
                .Range("M44").Formula = "=M42+'" & Sheets(x - 1).Name & "'!M44"
                .Range("M42").Formula = "=M40+'" & Sheets(x - 1).Name & "'!M42"
                .Range("M44").Formula = "=M42+'" & Sheets(x - 1).Name & "'!M44"
                .Range("M48").Formula = "=M46+'" & Sheets(x - 1).Name & "'!M48"
                .Range("M50").Formula = "=M48+'" & Sheets(x - 1).Name & "'!M50"
        End With
Next x
End Sub

kevinchengcw

猜一下，没实测

1. Sub 各种单耗()
   
2. Dim i&, x&, r&
   
3. i = Sheets.Count '计算工作表数
   
4. For x = 2 To i '建立每个表循环
   
5.     With Sheets(x)
   
6.         for r=30 to 50 step 2
   
7.             .range("c" & r).resize(,11).formular1c1="=r[-2]c+'" & Sheets(x - 1).Name & "'!rc"
   
8.         next r
   
9.     End With
   
10. Next x
    
11. End Sub

复制代码

半百

半百

Sub 各种单耗()
Dim i&, x&
i = Sheets.Count '计算工作表数
For x = 2 To i '建立每个表循环
    With Sheets(x)
        For j = 3 To 13  '循环C到M列
            n = 0
            For k = 1 To 8
                n = n + (k Mod 2) * 2 + 2
                .Cells(26 + n, j).Formula = "=" & Cells(24 + n, j).Address & "+'" & _
                Sheets(x - 1).Name & "'!" & Cells(26 + n, j).Address
            Next
        Next
    End With
Next x
End Sub

王跃峰

半百 发表于 2023-8-13 14:45

感谢帮助，" + '"提示缺少表达式

王跃峰

Sub 各种单耗()
Dim i&, x&
i = Sheets.Count '计算工作表数
For x = 2 To i '建立每个表循环
    With Sheets(x)
        For j = 3 To 13 '循环C到M列
            n = 0
            For k = 1 To 8
                n = n + (k Mod 2) * 2 + 2
                .Cells(26 + n, j).Formula ="="" & Cells(24 + n,j).Address & " + '" & _
                Sheets(x - 1).Name & "'!" & Cells(26 + n, j).Address
            Next
        Next
    End With
Next x
End Sub

半百

王跃峰 发表于 2023-8-13 16:15
感谢帮助，" + '"提示缺少表达式

再检查一下有没有拼写错误？我测试了没问题的。

王跃峰

Sub 各种单耗()
Dim i&, x&
i = Sheets.Count '计算工作表数
For x = 2 To i '建立每个表循环
    With Sheets(x)
        For j = 3 To 13 '循环C到M列
            n = 0
            For k = 1 To 8
                n = n + (k Mod 2) * 2 + 2
                .Cells(26 + n, j).Formula = "=" & Cells(24 + n, j).Address & "+'" & _
                Sheets(x - 1).Name & "'!" & Cells(26 + n, j).Address
            Next
        Next
    End With
Next x
End Sub

王跃峰

半百 发表于 2023-8-13 17:07
再检查一下有没有拼写错误？我测试了没问题的。

老师您好，假如这样怎么改
Sub 各种单耗()
Dim i&, x&
i = Sheets.Count '计算工作表数
For x = 2 To i '建立每个表循环
        With Sheets(x)
                .Range("C30").Formula = "=C28+'" & Sheets(x - 1).Name & "'!C30"
                .Range("C32").Formula = "=C28+'" & Sheets(x - 1).Name & "'!C32"
                .Range("C36").Formula = "=C34+'" & Sheets(x - 1).Name & "'!C36"
                .Range("C38").Formula = "=C34+'" & Sheets(x - 1).Name & "'!C38"
                .Range("C42").Formula = "=C40+'" & Sheets(x - 1).Name & "'!C42"
                .Range("C44").Formula = "=C40+'" & Sheets(x - 1).Name & "'!C44"
                .Range("C48").Formula = "=C46+'" & Sheets(x - 1).Name & "'!C48"
                .Range("C50").Formula = "=C46+'" & Sheets(x - 1).Name & "'!C50"
                .Range("D30").Formula = "=D28+'" & Sheets(x - 1).Name & "'!D30"
                .Range("D32").Formula = "=D28+'" & Sheets(x - 1).Name & "'!D32"
                .Range("D36").Formula = "=D34+'" & Sheets(x - 1).Name & "'!D36"
                .Range("D38").Formula = "=D34+'" & Sheets(x - 1).Name & "'!D38"
                .Range("D42").Formula = "=D40+'" & Sheets(x - 1).Name & "'!D42"
                .Range("D44").Formula = "=D40+'" & Sheets(x - 1).Name & "'!D44"
                .Range("D48").Formula = "=D46+'" & Sheets(x - 1).Name & "'!D48"
                .Range("D50").Formula = "=D46+'" & Sheets(x - 1).Name & "'!D50"

                .Range("E30").Formula = "=E28+'" & Sheets(x - 1).Name & "'!E30"
                .Range("E32").Formula = "=E28+'" & Sheets(x - 1).Name & "'!E32"
                .Range("E36").Formula = "=E34+'" & Sheets(x - 1).Name & "'!E36"
                .Range("E38").Formula = "=E34+'" & Sheets(x - 1).Name & "'!E38"
                .Range("E42").Formula = "=E40+'" & Sheets(x - 1).Name & "'!E42"
                .Range("E44").Formula = "=E40+'" & Sheets(x - 1).Name & "'!E44"
                .Range("E48").Formula = "=E46+'" & Sheets(x - 1).Name & "'!E48"
                .Range("E50").Formula = "=E46+'" & Sheets(x - 1).Name & "'!E50"
               
                .Range("F30").Formula = "=F28+'" & Sheets(x - 1).Name & "'!F30"
                .Range("F32").Formula = "=F28+'" & Sheets(x - 1).Name & "'!F32"
                .Range("F36").Formula = "=F34+'" & Sheets(x - 1).Name & "'!F36"
                .Range("F38").Formula = "=F34+'" & Sheets(x - 1).Name & "'!F38"
                .Range("F42").Formula = "=F40+'" & Sheets(x - 1).Name & "'!F42"
                .Range("F44").Formula = "=F40+'" & Sheets(x - 1).Name & "'!F44"
                .Range("F48").Formula = "=F46+'" & Sheets(x - 1).Name & "'!F48"
                .Range("F50").Formula = "=F46+'" & Sheets(x - 1).Name & "'!F50"
,,,,,,,,,,
        End With
Next x
End Sub

王跃峰

半百 发表于 2023-8-13 14:47
Sub 各种单耗()
Dim i&, x&
i = Sheets.Count '计算工作表数

老师，麻烦您帮忙看一下9楼怎么改