CODEWITHSUNDEEP
excelvba
Ravi Khambhati
Ravi Khambhati

Reputation: 743

FindNext of vba not working

This is very basic question and don't scream at me coz i am not a vba expert.

So here we go, I created below vba function

Public Function GetDuplicateCount(value As String) As Integer

    Dim counter As Integer

    counter = 0

    With Worksheets(1).Range("A:A")
        Set c = .Find(value, _
                    LookIn:=xlValues, _
                    LookAt:=xlWhole, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlNext, _
                    MatchCase:=False)

        If Not c Is Nothing Then
            firstAddress = c.Address
            Do
                counter = counter + 1
                Set c = .FindNext(c)
            Loop While Not c Is Nothing

        End If
    End With

    GetDuplicateCount = counter
End Function

Below is my excel values

A

1 IND

2 USA

3 CAN

4 IND

5 CAN

6 USA

Every time i search with any value it returns one don't know wny. Anything wrong with the function?

e.g. GetDuplicateCount("IND")

Upvotes: 3

Views: 15828

Answers (3)

SSlinky
SSlinky

Reputation: 488

This is an old question but in O365 it doesn't work because there's no exit condition for the first address so it loops until it blows up. Note that Is Nothing and .Address must be tested separately or you'll get an error if the range is nothing.

Below with added exit condition:

Public Function GetDuplicateCount(value As String) As Integer

    Dim counter As Integer
    Dim c As Range
    Dim firstAddress As String

    counter = 0

    With Worksheets(1).Range("A:A")
        Set c = .Find(value, _
                    LookIn:=xlValues, _
                    LookAt:=xlWhole, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlNext, _
                    MatchCase:=False)

        If Not c Is Nothing Then
            firstAddress = c.Address
            Do
                Debug.Print c.Address
                counter = counter + 1
                Set c = .FindNext(c)
                If c Is Nothing Then Exit Do
            Loop While Not c.Address = firstAddress

        End If
    End With

    GetDuplicateCount = counter
End Function

Upvotes: 0

Ravi Khambhati
Ravi Khambhati

Reputation: 743

Got it... finally

Two things FindNext is not workign so as suggested by @kazjaw i tried .find and here is the working code. Dont forget to give additional condition and that is "firstAddress <> c.Address"

Public Function GetDuplicateCount(value As String) As Integer

    Dim counter As Integer
    counter = 0

    With Worksheets(1).Range("A:A")
        Set c = .Find(value, _
                    LookIn:=xlValues, _
                    LookAt:=xlWhole, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlNext, _
                    after:=Cells(1, 1), _
                    MatchCase:=False)

        If Not c Is Nothing Then
            firstAddress = c.Address
            Do
                counter = counter + 1
                Set c = .Find(value, _
                    LookIn:=xlValues, _
                    LookAt:=xlWhole, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlNext, _
                    after:=c, _
                    MatchCase:=False)
            Loop While Not c Is Nothing And firstAddress <> c.Address

        End If
    End With

    GetDuplicateCount = counter
End Function

Upvotes: 4

tigeravatar
tigeravatar

Reputation: 26640

Why not use the native Countif function?

=COUNTIF(A:A,"IND")

Upvotes: 1

Related Questions