CODEWITHSUNDEEP
vbaexcelfind
user3714330
user3714330

Reputation: 689

VBA "find" function finds the wrong cell

I wrote a VBA "Sub" which creates a button, and that button does several jobs (I wrote down the code only for the first job, the other jobs are not relevant).

The first job is supposed to find the location of a cell which contains the string "Country". So it will find the cell which contains the string "Country", then it will find the column number (bc) and the row number (br) of this cell.

The cell with the value "Country" is in the "I7" cell, or in numeric format Cells(7,9). It works without error, but I get different results like bc=7 and br=1 ("G1"). Interesting point is that the cell, "G1", is empty.

Now it gives result like "I4", and it is also empty as you can see below.

The location of the target cell with the string "Country"

Does anyone have any idea about the bug? 

Private Sub Button1_Click()

    Dim b1 As Range
    Dim bc As Integer, br As Integer

    With Sheet5.Range("a1:z200")
    Set b1 = .Find(Name:="Country", LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows)

        If Not b1 Is Nothing Then
            bc = ActiveCell.Column
            br = ActiveCell.Row
            MsgBox (bc)

        End If
    End With

End Sub

Upvotes: 1

Views: 1804

Answers (2)

ChipsLetten
ChipsLetten

Reputation: 2953

The Find method does not change the active cell. Your code is mostly correct, just need to get the row & column from the b1 range object:

Private Sub Button1_Click()

    Dim b1 As Range
    Dim bc As Integer, br As Integer

    With Sheet5.Range("a1:z200")
    Set b1 = .Find(Name:="Country", LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows)

        If Not b1 Is Nothing Then
            bc = b1.Column
            br = b1.Row
            MsgBox (bc)

        End If
    End With

End Sub

BTW, you might want to think about using more meaningful variable names. In a month or so, the variable b1 probably won't mean anything to you when you come back to the code...

Upvotes: 1

user1016274
user1016274

Reputation: 4209

Add the activation if you are expecting the target cell to be active: 

If Not b1 Is Nothing Then
    b1.activate
    ...

Your code might continue to use activecell so this will fix it quickly.

Upvotes: 1

Related Questions