VBA基于另一个变量

问题描述：

使用VBA

=SUM(COUNTIF(E7,"Vac")+ COUNTIF(E7,"LWOP")+COUNTIF(R7,"Vac")+ COUNTIF(R7,"LWOP"))

但E7和R7将改变基于另一种叫做rCell.address

以下变量的公式是代码创建公式我有宏内，它是给了一个错误：

Range("a8").Formula = "=SUMIF(countif(" & rCell.Address & ", "VAC"" & "))"

当前宏是：

Sub Find() 

Dim strdate As String 
Dim rCell As Range 
Dim lReply As Long 

With Worksheets("Sheet1") 
strdate = .Range("a1").Value 
End With 

If strdate = "False" Then Exit Sub 
strdate = Format(strdate, "Short Date") 

On Error Resume Next 

    Set rCell = Cells.Find(What:=CDate(strdate), After:=Range("A1"), LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False) 

On Error GoTo 0 

If rCell Is Nothing Then 
    lReply = MsgBox("Date cannot be found. Try Again", vbYesNo) 
    If lReply = vbYes Then Run "FindDate": 
End If 

Range("a8").Formula = "=SUMIF(countif(" & rCell.Address & ",VAC" & "))" 
End Sub

答

我假设在这行代码出现在错误：Range("a8").Formula = "=SUMIF(countif(" & rCell.Address & ",VAC" & "))"。

你应该该行变为

Range("a8").Formula = "=SUM(COUNTIF(" & rCell.Address & "," & Chr(34) & "Vac" & Chr(34) & ")+ COUNTIF(" & rCell.Address & "," & Chr(34) & "LWOP" & Chr(34) & ")+COUNTIF(" & rCell.Offset(0, 13).Address & "," & Chr(34) & "Vac" & Chr(34) & ")+ COUNTIF(" & rCell.Offset(0, 13).Address & "," & Chr(34) & "LWOP" & Chr(34) & "))"

有更好的回答感谢更新到KS Sheon

Range("a8").Formula = "=SUM(COUNTIF(" & rCell.Address & ",""Vac"")+ COUNTIF(" & rCell.Address & ",""LWOP"")+COUNTIF(" & rCell.Offset(0, 13).Address & ",""Vac"")+ COUNTIF(" & rCell.Offset(0, 13).Address & ",""LWOP""))"

替代的CHR（34）是双引号（ “”）。它会像'= sum（countif（“＆[a1] .Address＆”，“”VAC“”））“ ' – Rosetta

啊，谢谢！非常感谢@KSSheon;我用更简洁的代码更新了我的答案。 –

欢迎，并有美好的一天:) – Rosetta

答

SUMIF在这种情况下是错误的。

Range("a8").Formula = "=SUMIF(countif(" & rCell.Address & ",VAC" & "))"

你应该使用SUMIF这样

Range("a8").Formula =SUMIF(B2:B25,">5")

VBA基于另一个变量

相关推荐