CODEWITHSUNDEEP
vbaexcelexcel-2010
FaytZero
FaytZero

Reputation: 43

Use Range Find Method in a specific column

I have been tinkering with this for a while now, I have the below code working but it searches more than I would like it to. I would like it to just search column C and not the entire sheet. I have tried changing out the Cells.Find to Range("C1:C10000").Find but it returns no matches.

Really stumped on this one.

Dim r As Range

Set r = Sheets("State Agent List").Range("C1:C10000").Find(What:=ComboBox22.Value, _
            After:=ActiveCell, LookAt:=xlPart, SearchOrder:=xlByRows)

If Not r Is Nothing Then
    r.Select
    ActiveWindow.ScrollRow = ActiveCell.Row
Else
    MsgBox "Location not listed."
End If

Upvotes: 3

Views: 5092

Answers (2)

brettdj
brettdj

Reputation: 55672

I would try it like this

  • ensure you start searching from C1 in case there are multiple matches (given you are setting a scrollwindow)
  • xlFormulas finds cells in hidden rows, xlValues skips them

  • no need for the Select

    Dim rng1 As Range
Set rng1 = Sheets("State Agent List").Range("C1:C10000").Find(CStr(ComboBox22.Value), [c10000], xlFormulas, xlPart, , xlNext)

If Not rng1 Is Nothing Then
    ActiveWindow.ScrollRow = rng1.Row
Else
    MsgBox "Location not listed."
End If

Upvotes: 0

L42
L42

Reputation: 19727

Your problem is the use of ActiveCell which if happens to be not within C1:C10000 will return nothing. Try this:

Dim searchRng As Range: Set searchRng = Sheets("State Agent List").Range("C1:C10000")
Dim r As Range
Set r = searchRng.Find(What:=ComboBox22.Value, After:=searchRng(searchRng.Count))

The argument searchRng(searchRng.Count) pertains to the last cell of the Range you're working on. It works but it can be written explicitly this way:

searchRng.Cells(searchRng.Cells.Count)

Why do we need to set the After argument to the last cell?
Main reason is for the search to begin from the very first cell. HTH

Upvotes: 1

Related Questions