excel - else if 语句默认为最后一个条件 vba

Question

我目前正在编写一个宏，它将读取一列日期，检查每个项目的月份，将数字记录在变量中，并将最终数字放入单元格中。该程序正在查看包含 2999 个项目的列。该列未完全填满。之所以选择该数字，是因为项目的数量可能如此之多。

for each 循环检查每个单元格是哪个月，并将该月的变量增加 1。但是，最后，December 变量约为 2500。只有大约 500 行填充。月份总和为 2999(正在搜索的行数)。我不确定它为什么这样做。我尝试添加“其他”条件。它甚至没有达到那么远。 A15 从来没有充满“ bat 侠”。我必须缺少一些简单的东西。帮助？

Dim rng3 As Range
Dim JanCount As Integer
Dim FebCount As Integer
Dim MarCount As Integer
Dim AprCount As Integer
Dim MayCount As Integer
Dim JunCount As Integer
Dim JulCount As Integer
Dim AugCount As Integer
Dim SepCount As Integer
Dim OctCount As Integer
Dim NovCount As Integer
Dim DecCount As Integer
Dim dateValue As Date
Dim monthInt As Integer

Set rng3 = Sheets("Sheet2").Range("K2:K3000")

For Each cell In rng3
dateValue = cell.Value
monthInt = month(dateValue)

If monthInt = 1 Then
    JanCount = JanCount + 1

ElseIf monthInt = 2 Then
    FebCount = FebCount + 1

ElseIf monthInt = 3 Then
    MarCount = MarCount + 1

ElseIf monthInt = 4 Then
    AprCount = AprCount + 1

ElseIf monthInt = 5 Then
    MayCount = MayCount + 1

ElseIf monthInt = 6 Then
    JunCount = JunCount + 1

ElseIf monthInt = 7 Then
    JulCount = JulCount + 1

ElseIf monthInt = 8 Then
    AugCount = AugCount + 1

ElseIf monthInt = 9 Then
    SepCount = SepCount + 1

ElseIf monthInt = 10 Then
    OctCount = OctCount + 1

ElseIf monthInt = 11 Then
    NovCount = NovCount + 1

ElseIf monthInt = 12 Then
    DecCount = DecCount + 1

Else
    Sheets("Sheet1").Range("A15") = "Batman"

End If

Next cell

Sheets("Sheet1").Range("A10") = "Bugs in Jan"
Sheets("Sheet1").Range("B10") = "Bugs in Feb"
Sheets("Sheet1").Range("C10") = "Bugs in Mar"
Sheets("Sheet1").Range("D10") = "Bugs in Apr"
Sheets("Sheet1").Range("E10") = "Bugs in May"
Sheets("Sheet1").Range("F10") = "Bugs in Jun"
Sheets("Sheet1").Range("G10") = "Bugs in Jul"
Sheets("Sheet1").Range("H10") = "Bugs in Aug"
Sheets("Sheet1").Range("J10") = "Bugs in Oct"
Sheets("Sheet1").Range("K10") = "Bugs in Nov"
Sheets("Sheet1").Range("L10") = "Bugs in Dec"

Sheets("Sheet1").Range("A11") = JanCount
Sheets("Sheet1").Range("B11") = FebCount
Sheets("Sheet1").Range("C11") = MarCount
Sheets("Sheet1").Range("D11") = AprCount
Sheets("Sheet1").Range("E11") = MayCount
Sheets("Sheet1").Range("F11") = JunCount
Sheets("Sheet1").Range("G11") = JulCount
Sheets("Sheet1").Range("H11") = AugCount
Sheets("Sheet1").Range("I11") = SepCount
Sheets("Sheet1").Range("J11") = OctCount
Sheets("Sheet1").Range("K11") = NovCount
Sheets("Sheet1").Range("L11") = DecCount

Answer 1

尝试先检查 IsDate()单元格值是否可以解释为日期。

而且..只是一个让整个事情变得更容易的建议:

Dim Rng3 As Range
Dim BugCount(1 To 12)


Set Rng3 = Sheets("Sheet2").Range("K2:K3000")

For Each cell In Rng3
    If IsDate(cell.Value) Then
        BugCount(Month(cell.Value)) = BugCount(Month(cell.Value)) + 1
    End If
Next

For monthctr = 1 To 12
    Sheets("Sheet1").Range("A10").Offset(0, monthctr - 1).Value = "Bugs in " & Format(DateSerial(2013, monthctr, 1), "MMM")
    Sheets("Sheet1").Range("A11").Offset(0, monthctr - 1).Value = BugCount(monthctr)

Next