首页 文章

将长If / Then公式转换为Excel宏

提问于
浏览 次
0

我有一个依赖于很多变量的宏 . 我设置它的方式（因为我知道公式比宏更好）是一个 =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

我知道有一种方法将它们串在一起......我只是不确定如何 . 甚至没有多列......这就是我如何知道如何让它工作 .

excel vba excel-vba

1 回答

  • 0

    你的VBA正处于正确的轨道上，但你正在尝试在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"")))))))"

    对此：

    Dim cel as range
offset(row,col)
set cel = ActiveCell
if cel.offset(0,-8) > 1 and cel.offset(0,-1) = 1 and cel.offset(0,-7) > 0 then
  cel.interior.color = vbYellow
elseif cel.offset(0,-8) > 1 and cel.offset(0,-1) = 1 and cel.offset(0,-7) = 0 then
  cel.interior.color = vbRed
elseif cel.offset(0,-8) > 1 and cel.offset(0,-1) > 1 and cel.offset(0,-7) > 0 then
  cel.interior.color = vbBlue
elseif ucase(cel.offset(0,-3)) = "YES" and cel.offset(0,-7) > 0 then
  cel.interior.color = vbBlue
elseif ucase(cel.offset(0,-3)) = "NO" and cel.offset(0,-7) > 0 then
  cel.interior.color = vbRed
elseif cel.offset(0,-7) < 0 then
  cel.interior.color = vbOrange
elseif cel.offset(0,-7) = 0 then
  cel.interior.color = vb.Red
End If

    注意：

    • vbOrange可能不是预定义的颜色 . 您可能必须指定要到达的RGB . 这样做是为了让你前进 .

    • set cel = ActiveCell 很可能不是你真正想要的，但是你没有必要弄清楚如何正确地分配这个 .

    • 使用此代码，我认为您应该能够消除多列的使用，因为您具有适当的VBA语法而不是公式 .

    回复于

相关问题