Excel VBA Find Function Side Effect?

Question

All. I am new to VBA and MS EXCEl 2010. I just started to play with the macro module in EXCEL yesterday, almost zero experience.

What I was trying to do is described as follows. First search for value records in a range in sheet1, then for each cell I found, I locate that row and pull out another cell value at that row. Using this value to do another search in a range in sheet2. I'll point out the problem I am having after my code. Here is the pesudcode.

 Dim Found As Range
 With RangeInSheet1
        Set Found = .Find(value1)
        If Not Found Is Nothing Then
            firstAddress = Found.Address
            Do  
                With RangeInSheet2
                    ColumnIndex = .Find(value2).Column
                End With
                Set Found = .FindNext(Found)
            Loop While Not Found Is Nothing And Found.Address <> firstAddress
        End If
End With

value1 is the key I used to search in RangeSheet1, and value2 in RangeSheet2. The above code goes through every record I found for value1 in sheet 1 and do another search in Sheet2.

Now let's Say value1 = 1, value2 =2007, and there are 5 records that contains value1 in sheet 1. The problem arises from this line of code "ColumnIndex = .Find(value2).Column".

Supposedly, for all the five found records, value of Found should always be 1 after "Set Found = .FindNext(Found)" is executed . But, after I added this ColumnIndex code, value of Found is set to 2007, which is so weird to me. Anyone knows what the problem is? Any help will be appreciate. I really need to keep the Found behaves "normal" as I want.

If anything is unclear, please let me know

Siddharth Rout Siddharth Rout · Accepted Answer · 2013-11-09T07:45:34

.Find/.Findnext remembers the last setting. And hence it is always advisable to completely Specify the parameters. specially After:= parameter. It will also remember what was your last search term i.e What:=

Here is a demonstration on how to work with .Find/.Findnext

Also do not use Value2 as a variable. It is a reserved word. Instead of using Value1 and Value2, I am using sSearch1 and sSearch2 in the below code

Let's say your sheets look like this

enter image description here

Now Try this code

Sub Sample()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim rngWs1 As Range, rngWs2 As Range
    Dim aCell As Range, bCell As Range, cCell As Range, dCell As Range, eCell As Range, cl As Range
    Dim sSearch1, sSearch2

    Set ws1 = ThisWorkbook.Sheets("Sheet1")
    Set rngWs1 = ws1.Range("A1:A10")

    Set ws2 = ThisWorkbook.Sheets("Sheet2")
    Set rngWs2 = ws2.Cells

    With ws1
        For i = 1 To 10
            sSearch1 = .Range("A" & i).Value
            Set aCell = .Range("A" & i)

            If Len(Trim(sSearch1)) <> 0 Then
                Set aCell = rngWs1.Find(What:=sSearch1, After:=aCell, LookIn:=xlValues, _
                LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=False, SearchFormat:=False)

                If Not aCell Is Nothing Then
                    sSearch2 = aCell.Offset(, 1).Value

                    With ws2
                        Set bCell = rngWs2.Find(What:=sSearch2, After:=.Range("A1"), LookIn:=xlValues, _
                                LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                                MatchCase:=False, SearchFormat:=False)

                        If Not bCell Is Nothing Then
                            Debug.Print "For " & sSearch1 & ", " & sSearch2 & " Found in " & bCell.Address

                            Set cCell = bCell

                            Do
                                Set bCell = rngWs2.FindNext(After:=bCell)

                                If Not bCell Is Nothing Then
                                    If bCell.Address = cCell.Address Then Exit Do
                                    Debug.Print "For " & sSearch1 & ", " & sSearch2 & " Found in " & bCell.Address
                                Else
                                    Exit Do
                                End If
                            Loop
                        End If
                    End With
                End If
            End If
        Next
    End With
End Sub

This is the result that we get.

enter image description here

Excel VBA Find Function Side Effect?

1 Answers