时间戳

首页 > 解决方案 > 到达最后一个数据时逻辑中断

excel - 到达最后一个数据时逻辑中断

问题描述

点击按钮时，我想计算同一列中每一行中数据的冗余。它将对数据进行排序，并计算并显示该单词在该列中重复的次数。但是当它到达最后一个单词时，它不会计算并显示该单词有多少冗余。这是我的代码

Dim start As Integer
Dim last As Integer
Dim word As String
Dim line As Integer
Dim count As Integer

line = 5
count = 1

With Columns("A")
    .Find(what:="*", after:=.Cells(1, 1), LookIn:=xlFormulas).Activate
    start = ActiveCell.Row
    last = ActiveCell.End(xlDown).Row
End With

Range(Cells(start, 1), Cells(last, 1)).Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo

For i = start To last
    word = Cells(i, 1).Value
    Cells(line, 6) = word
    For j = i + 1 To last
        If Cells(j, 1).Value = word Then
            count = count + 1
        Else
            Cells(line, 7) = count
            line = line + 1
            count = 1
        End If
        Exit For
    Next j
Next i

我是vba的新手。请帮忙。提前致谢。

标签： excelvba

解决方案

逻辑缺陷在于Exit For破坏了循环的目的

For j = i + 1 To last
    If Cells(j, 1).Value = word Then
        count = count + 1
    Else
        Cells(line, 7) = count
        line = line + 1
        count = 1
    End If
    Exit For '<--- this will always makes the loop exit after first iteration
Next j

因此您应该消除循环并仅对其第一次迭代运行检查

If Cells(i + 1, 1).Value = word Then
    count = count + 1
Else
    Cells(line, 7) = count
    line = line + 1
    count = 1
End If

推荐阅读