时间戳

首页 > 解决方案 > 奇数/偶数行的交替公式

excel - 奇数/偶数行的交替公式

问题描述

我试图在偶数行上写一个公式，在同一列的奇数行上写一个不同的公式。搜索这个网站我发现了以下代码：

Sub OddRowAlert()
With Range("B16:B100")
    .Formula = "=IF((MOD(ROW(B16),2)),""Odd"",""Even"")"
    .Formula = .Value
End With
End Sub

这适用于文本或简单的公式。但是，当我用我的公式替换“奇数”或“偶数”时，我会收到一个

运行时错误“1004”：应用程序定义或对象定义错误。

这是我正在尝试的代码：

Sub Daily_Analysis()
'
' Daily_Analysis Macro
'
Lastrow = Range("B" & Rows.Count).End(xlUp).Row

Range("L7:L" & Lastrow).Formula = "=Text(A7,""dddd"")"

With Range("M7:M" & Lastrow)
    .Formula = "=IF((MOD(ROW(M7),2)),""=IFS(L7=""Saturday"","""",L7=""Sunday"","""",L7<>L6,""Number of times over 75%"",L7=L6,"""")"",""=IFS(L7=""Saturday"","""",L7=""Sunday"","""",L6<>L5,COUNTIFS(D7:D103,"">75%"",K7:K103,""Working"")+COUNTIFS(G7:G103,"">75%"",K7:K103,""Working""),L6=L5,"""")"")"
    .Formula = .Value
End With

With Range("N7:N" & Lastrow)
     .Formula = "=IF((MOD(ROW(N7),2)),""=IFS(L7=""Saturday"","""",L7=""Sunday"","""",L7<>L6,""Percentage of the day"",L7=L6,"""")"",""=IFS(L7=""Saturday"","""",L7=""Sunday"","""",L7<>L6,M8/COUNTIFS(K7:K102,""working"",L7:L102,L7),L7=L6,"""")"")"
     .Formula = .Value
End With

With Range("O7:O" & Lastrow)
     .Formula = "=IF((MOD(ROW(O7),2)),""=IFS(L7=""Saturday"","""",L7=""Sunday"","""",L7<>L6,""hours of poor performance"",L7=L6,"""")"",""=IFS(L7=""Saturday"","""",L7=""Sunday"","""",L7<>L6,(15*M8)/60,L7=L6,"""")"")"
     .Formula = .Value
End With

'
End Sub

标签： excelvba

解决方案

如评论中所述，嵌套公式不会""围绕它们并且不=，只是嵌套它们：

Sub Daily_Analysis()
'
' Daily_Analysis Macro
'
Lastrow = ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row

ActiveSheet.Range("L7:L" & Lastrow).Formula = "=Text(A7,""dddd"")"

With ActiveSheet.Range("M7:M" & Lastrow)
    .Formula = "=IF((MOD(ROW(M7),2)),IFS(L7=""Saturday"","""",L7=""Sunday"","""",L7<>L6,""Number of times over 75%"",L7=L6,""""),IFS(L7=""Saturday"","""",L7=""Sunday"","""",L6<>L5,COUNTIFS(D7:D103,"">75%"",K7:K103,""Working"")+COUNTIFS(G7:G103,"">75%"",K7:K103,""Working""),L6=L5,""""))"
    .Value = .Value
End With

With ActiveSheet.Range("N7:N" & Lastrow)
     .Formula = "=IF((MOD(ROW(N7),2)),IFS(L7=""Saturday"","""",L7=""Sunday"","""",L7<>L6,""Percentage of the day"",L7=L6,""""),IFS(L7=""Saturday"","""",L7=""Sunday"","""",L7<>L6,M8/COUNTIFS(K7:K102,""working"",L7:L102,L7),L7=L6,""""))"
     .Value = .Value
End With

With ActiveSheet.Range("O7:O" & Lastrow)
     .Formula = "=IF((MOD(ROW(O7),2)),IFS(L7=""Saturday"","""",L7=""Sunday"","""",L7<>L6,""hours of poor performance"",L7=L6,""""),IFS(L7=""Saturday"","""",L7=""Sunday"","""",L7<>L6,(15*M8)/60,L7=L6,""""))"
     .Value = .Value
End With

'
End Sub

顺便说一句，取出类似的项目并重新排列我们可以缩短公式：

Sub Daily_Analysis()
'
' Daily_Analysis Macro
'
Lastrow = ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row

ActiveSheet.Range("L7:L" & Lastrow).Formula = "=Text(A7,""dddd"")"

With ActiveSheet.Range("M7:M" & Lastrow)
    .Formula = "=IF(OR(WEEKDAY(A7,2)>5,l7=l6),"""",IF(Mod(row(),2),""Number of times over 75%"",COUNTIFS(D7:D103,"">75%"",K7:K103,""Working"")+COUNTIFS(G7:G103,"">75%"",K7:K103,""Working"")))"
    .Value = .Value
End With

With ActiveSheet.Range("N7:N" & Lastrow)
     .Formula = "=IF(OR(WEEKDAY(A7,2)>5,l7=l6),"""",IF(Mod(row(),2),""Percentage of the day"",M8/COUNTIFS(K7:K102,""working"",L7:L102,L7)))"
     .Value = .Value
End With

With ActiveSheet.Range("O7:O" & Lastrow)
     .Formula = "=IF(OR(WEEKDAY(A7,2)>5,l7=l6),"""",IF(Mod(row(),2),""hours of poor performance"",L7<>L6,(15*M8)/60))"
     .Value = .Value
End With

'
End Sub

推荐阅读