我有一个依赖于很多变量的宏 . 我设置它的方式(因为我知道公式比宏更好)是一个 =if(and((etc etc)) 语句的字符串,它可以工作,但是当我运行它时,它需要大约20秒 . 我知道有's a faster way, but I' m坚持将公式转换为If / Then以使其更快 .
它的设置方式是,如果满足所有特定条件,颜色将填充在N列中 . 然后,它使用 ISNUMBER(SEARCH(""Red"",$N1))=TRUE 公式应用条件格式以突出显示基于颜色 .
我的所有条件公式如下 . 我知道可能有一种方法将它们全部串成一个,我只是不知道它 .
F列 - 总和产品: =SUMPRODUCT((A:A=A20)*(E:E=*"N/A"*))
VBA: "=SUMPRODUCT((C[-5]=RC[-5])*(C[-1]=""N/A""))"
(它实际上显示文本“N / A” - 它没有返回错误)
G栏 - 从P列查找美元金额:
=LOOKUP(A20,O:O,P:P)
VBA: "=LOOKUP(RC[-6],C[8],C[9])"
列K - 确定列E是否以5开头:
=IF(LEFT(C20,1)="5","Yes","No")
VBA: "=IF(LEFT(RC[-8],1)=""5"",""Yes"",""No"")"
M栏 - (Count occurances in column A) * (Count Column F > 1) * (Dollar amount in D > 0)) :
=SUMPRODUCT((A:A=A20)*(F:F>1)*(D:D>0))
VBA: "=SUMPRODUCT((C[-12]=RC[-12])*(C[-7]>1)*(C[-9]>0))"
列N - 为每个条件指定颜色值并应用于列N:
=IF(AND((F20>1),(M20=1),(G20>0)),"Yellow",IF(AND((F20>1),(M20=1),(G20=0)),"Red",IF(AND((F20>1),(M20>1),(G20>0)),"Blue",IF(AND((K20="Yes"),(G20>0)),"Blue",IF(AND((K20="No"),(G20>0)),"Red",IF(G20<0,"Orange",IF(G20=0,"Red","Next")))))))
VBA: "=IF(AND((RC[-8]>1),(RC[-1]=1),(RC[-7]>0)),""Yellow""," & _ "IF(AND((RC[-8]>1),(RC[-1]=1),(RC[-7]=0)),""Red""," & _ "IF(AND((RC[-8]>1),(RC[-1]>1),(RC[-7]>0)),""Blue""," & _ "IF(AND((RC[-3]=""Yes""),(RC[-7]>0)),""Blue""," & _ "IF(AND((RC[-3]=""No""),(RC[-7]>0)),""Red""," & _ "IF(RC[-7]<0,""Orange""," & _ "IF(RC[-7]=0,""Red"",""Next"")))))))"
然后为了应用突出显示,我有:
Columns("A:E").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=ISNUMBER(SEARCH(""Blue"",$N1))=TRUE"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.Color = 15773696
End With
我知道有一种方法将它们串在一起......我只是不确定如何 . 甚至没有多列......这就是我如何知道如何让它工作 .