时间戳

首页 > 解决方案 > 在 VBA 中复制 Excel 函数

excel - 在 VBA 中复制 Excel 函数

问题描述

我有一个excel函数如下 -

IF(B3="","",IF(AND(G3="NA",F3="Qualified"),"New to Qualified",
IF(AND(G3="NA",F3<>"Qualified"),CONCATENATE("New to Qualified and ",F3),
IF(AND(G3<>"NA",F3="Qualified"),IF(H3<>G3,"TCV Change","Same"),
IF(AND(G3<>"NA",F3<>"Qualified"),IF(H3="NA","TCV Change",IF(H3<>G3,CONCATENATE("TCV Change and ",F3),F3)))))))

我正在尝试在 VBA 中复制相同的内容 -

Sub CC()

Dim str1 As String
Dim str2 As String

str1 = "New to Qualified and" & Range("F3:F100")
str2 = "TCV Change and" & Range("F3:F100")


Range("J3:J100").Value = IIf(Application.WorksheetFunction.And(Range("G3:G100") = "NA", Range("F3:F100") = "Qualified"), "New to Qualified", _
IIf(Application.WorksheetFunction.And(Range("G3:G100") = "NA", Range("F3:F100") <> "Qualified"), str1, _
IIf(Application.WorksheetFunction.And(Range("G3:G100") <> "NA", Range("F3:F100") = "Qualified"), IIf(Range("H3:H100") <> Range("G3:G100"), "TCV Change", "Same"), _
IIf(Application.WorksheetFunction.And(Range("G3:G100") <> "NA", Range("F3:F100") <> "Qualified"), IIf(Range("H3:H100") = "NA", "TCV Change", IIf(Range("H3:H100") <> Range("G3:G100"), str2, Range("F3:F100")))))))


End Sub

但是我在最后一行遇到错误 - “编译错误 - 参数不是可选的”

请帮忙。

标签： excelvbaexcel-formula

解决方案

不确定它应该如何与范围一起工作，但我试图在单元格级别修复您的代码

尝试使用这个：

str1 = "New to Qualified and" & Range("F7")
str2 = "TCV Change and" & Range("F7")

...

With Application.WorksheetFunction
    Range("J7").Value = _
    IIf(.And(Range("G7") = "NA", Range("F7") = "Qualified"), "New to Qualified", _
    IIf(.And(Range("G7") = "NA", Range("F7") <> "Qualified"), str1, _
    IIf(.And(Range("G7") <> "NA", Range("F7") = "Qualified"), IIf(.And(Range("H7") <> Range("G7")), "TCV Change", "Same"), _
    IIf(.And(Range("G7") <> "NA", Range("F7") <> "Qualified"), IIf(Range("H7") = "NA", "TCV Change", IIf(Range("H7") <> Range("G7"), str2, Range("F7"))), ""))))
End With

推荐阅读