InputBox变量在Countif公式中未被识别

Question

我有一个宏，可以使用countif，用不同年份表示的数据进行计算。我希望能够将搜索的年份设置在输入框中，这样您就可以根据具体情况更改您的搜索年份。我的变量不是带进公式的。

Sub FullGiftCalculation()
    Dim ws As Worksheet
    Dim xYear As Integer 
    xYear = InputBox("What is the Campaign Year?")

    For Each ws In Worksheets
        ws.Select

        ActiveSheet.Range("T10").Value = "Total Constituents"
        ActiveSheet.Range("T11").Value = "Total Gifts Open"
        ActiveSheet.Range("T12").Value = "Total Gifts Closed"
        ActiveSheet.Range("T13").Value = "% Closed"
        ActiveSheet.Range("V10").Formula = "=countif(B1:B5000,xYear)"
        ActiveSheet.Range("V12").Formula = "=countifs(B1:B5000,xYear,E1:E5000,""C-Pledged"")"
        ActiveSheet.Range("V11").Formula = "=V10-V12"
        ActiveSheet.Range("V13").Formula = "=V12/V10"
        Range("V13").NumberFormat = "0.00%"
    Next ws    
End Sub

当我在宏中执行时，当我悬停在"xYear =.“行时，我可以看到我的set变量。但不是在我的公式里。运行时的输出为零。

Answer 1

当您通过VBA放置公式时，您可以将公式以字符串的形式放置。

=可数(B1:B 5000，xYear)

但是正如您在上面的字符串中所看到的，xYear是一个变量，当您需要在任何字符串中添加一个变量时，您需要使用&符号连接这个变量，然后这样做.

=countif(B1:B 5000，“& xYear &”)

也就是说你把绳子分成两部分..。

“=countif(B1:B 5000，”和")“，然后使用字符串连接符号&在变量的两边，将变量放置在两个字符串部分之间。

就您的代码而言，如果要在工作簿中的每个可用工作表上放置相同的公式，甚至不需要选择工作表。

你可以试试下面的代码..。

Sub FullGiftCalculation()
    Dim ws As Worksheet
    Dim xYear As Integer
    xYear = InputBox("What is the Campaign Year?")
    For Each ws In Worksheets
        With ws
            .Range("T10").Value = "Total Constituents"
            .Range("T11").Value = "Total Gifts Open"
            .Range("T12").Value = "Total Gifts Closed"
            .Range("T13").Value = "% Closed"
            .Range("V10").Formula = "=countif(B1:B5000," & xYear & ")"
            .Range("V12").Formula = "=countifs(B1:B5000," & xYear & ",E1:E5000,""C-Pledged"")"
            .Range("V11").Formula = "=V10-V12"
            .Range("V13").Formula = "=V12/V10"
            .Range("V13").NumberFormat = "0.00%"
        End With
    Next ws
End Sub

Answer 2

您需要将变量值插入论坛，如下所示：

Sub FullGiftCalculation()
    Dim ws As Worksheet
    Dim xYear As Integer 
    xYear = InputBox("What is the Campaign Year?")

    For Each ws In Worksheets
        ws.Select

        ActiveSheet.Range("T10").Value = "Total Constituents"
        ActiveSheet.Range("T11").Value = "Total Gifts Open"
        ActiveSheet.Range("T12").Value = "Total Gifts Closed"
        ActiveSheet.Range("T13").Value = "% Closed"
        ActiveSheet.Range("V10").Formula = "=countif(B1:B5000,"& xYear &")"
        ActiveSheet.Range("V12").Formula = "=countifs(B1:B5000,"& xYear &",E1:E5000,""C-Pledged"")"
        ActiveSheet.Range("V11").Formula = "=V10-V12"
        ActiveSheet.Range("V13").Formula = "=V12/V10"
        Range("V13").NumberFormat = "0.00%"
    Next ws    
End Sub