CODEWITHSUNDEEP
excelms-accessvba
techV
techV

Reputation: 935

Find and FindNext in VBA

I am working on Excel macro. What i need when getting data from another excel sheet, code should first check if there is any other row with the same FundName and if found then conditions apply.

I am just giving the sample of Excel Sheet from which the FundId is to be checked :

S.No    Funds
1        A
2        B
3        C
4        D
5        A

Code is given below:

Set shtData = wbraw.Sheets(1) ' this line is correct

Set CCell = shtData.Cells.Find("Funds", LookIn:=xlValues, LookAt:=xlWhole).Offset(1, 0)

Set DCell = CCell.End(xlDown)

Dim SearchString as String
SearchString  = "A"  

Set FindRow = shtData.Range(CCell, DCell).Find(What:=SearchString, LookIn:=xlValues, _
                LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=False, SearchFormat:=False)

Set NextRow = shtData.Range(CCell, DCell).FindNext(After:=FindRow)

Above two lines in code not working as i want it should be. Let say if SearchString is set to "A" then FindRow and NextRow both should have the value. And if SearchString is set to "B" then as per given excel sheet FindRow should have the value but NextRow returns Nothing so that I can apply my conditions.

Please if anyone can help me.

Upvotes: 0

Views: 3880

Answers (2)

ignotus
ignotus

Reputation: 658

Find will use the first cell of Range for the After parameter, if it is not specified, therefore the search is started after B2, and thus the first cell it finds is B6. If the order is important for you then call Find with the last cell provided as After: 

    Dim counter As Integer
    counter = 0

    With shtData.Range(CCell, DCell)
        Set c = .Find(SearchString, LookIn:=xlValues, LookAt:=xlWhole, After:=DCell)
        If Not c Is Nothing Then
            firstAddress = c.Address
            Do
                counter = counter + 1
                Debug.Print "The next match #" & counter & " is " & c.Address

                Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address <> firstAddress
        End If
    End With

Upvotes: 1

SierraOscar
SierraOscar

Reputation: 17637

Replace this:

Set FindRow = shtData.Range(CCell, DCell).Find(What:=SearchString, LookIn:=xlValues, _
                LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=False, SearchFormat:=False)

Set NextRow = shtData.Range(CCell, DCell).FindNext(After:=FindRow)

With:

If WorksheetFunction.CountIf(CCell.EntireColumn, SearchString) > 1 Then
   'Duplicate found, do something here
Else
   'Unique string, do something here
End If

Or 

If Evaluate("COUNTIF(" & CCell.EntireColumn.Address & "," & SearchString & ")") > 1 Then
   'Duplicate found, do something here
Else
   'Unique string, do something here
End If

Upvotes: 1

Related Questions