How to find a value in an excel column by vba code Cells.Find

Question

I have to find a value celda in an Excel sheet. I was using this vba code to find it:

Set cell = Cells.Find(What:=celda, After:=ActiveCell, LookIn:= _
    xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:= _
    xlNext, MatchCase:=False, SearchFormat:=False)


If cell Is Nothing Then
    'do it something

Else
    'do it another thing
End If

The problem is when I have to find the value only in a excel column. I find it with next code:

    Columns("B:B").Select
    Selection.Find(What:="VA22GU1", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate

But I don't know how to adapt it to the first vba code, because I have to use the value nothing.

If you simply want to know if the value exists somewhere in the range, it is quicker execution (worth it if checking hundreds of values) to use an Excel formula. If celda is a number for example, you could use IF Evaluate("COUNTIF(Sheet1!A1:A1000," & celda & ")") > 0 THEN ... — lessthanideal, Nov 06 '13 at 14:32
@eli-k: [Here](http://www.siddharthrout.com/index.php/2018/01/05/find-and-findnext-in-excel-vba/) is the updated link :) — Siddharth Rout, Mar 07 '19 at 03:29

score 62 · Accepted Answer · edited Sep 30 '19 at 07:35

62

Just use

Dim Cell As Range
Columns("B:B").Select
Set cell = Selection.Find(What:="celda", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)

If cell Is Nothing Then
    'do it something

Else
    'do it another thing
End If

edited Sep 30 '19 at 07:35

A. Kiyoshi

61
1
13

answered Feb 18 '13 at 08:02

Andrey Gordeev

30,606
13
135
162

6

If you use the code above and get an error, make sure you add the line `Dim cell As Range` above the code. – Ben May 21 '18 at 18:20
1

NBD, but the variable "Cell" should be capitalized when declared and in code. – BrettFromLA Oct 12 '21 at 21:18

score 13 · Answer 2 · edited Aug 27 '15 at 21:47

13

Just for sake of completeness, you can also use the same technique above with excel tables.

In the example below, I'm looking of a text in any cell of a Excel Table named "tblConfig", place in the sheet named Config that normally is set to be hidden. I'm accepting the defaults of the Find method.

Dim list As ListObject
Dim config As Worksheet
Dim cell as Range


Set config = Sheets("Config")
Set list = config.ListObjects("tblConfig")

'search in any cell of the data range of excel table
Set cell = list.DataBodyRange.Find(searchTerm)

If cell Is Nothing Then
    'when information is not found
Else
    'when information is found
End If

edited Aug 27 '15 at 21:47

Community

1
1

answered Jul 31 '14 at 18:07

Mário Meyrelles

1,594
21
26

I always find it best not to change the selection in VBA scripts unless the change in selection is part of the desired final result. Of course this particular answer requires changing the workbook to use a defined table. – robartsd Jul 29 '19 at 18:19
I am planning to use this method for one of my projects. I am looking through a specific table. Within this table, there is a cell with the value "Dispatch:". The actual value that I am looking for is either one or two cells to the right of this particular cell. How would I go about returning the value in the subsequent cell? (i.e. A1 has value "Dispatch:", C1 has a numeric value of the specific dispatch number. I want to return the numeric value.) – Sean Donnahoe Oct 20 '22 at 20:51

score 10 · Answer 3 · answered Jul 29 '19 at 20:48

I'd prefer to use the .Find method directly on a range object containing the range of cells to be searched. For original poster's code it might look like:

Set cell = ActiveSheet.Columns("B:B").Find( _
    What:=celda, _
    After:=ActiveCell _
    LookIn:=xlFormulas, _
    LookAt:=xlWhole, _
    SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, _
    MatchCase:=False, _
    SearchFormat:=False _
)

If cell Is Nothing Then
    'do something
Else
    'do something else
End If

I'd prefer to use more variables (and be sure to declare them) and let a lot of optional arguments use their default values:

Dim rng as Range
Dim cell as Range
Dim search as String

Set rng = ActiveSheet.Columns("B:B")
search = "String to Find"
Set cell = rng.Find(What:=search, LookIn:=xlFormulas, LookAt:=xlWhole, MatchCase:=False)

If cell Is Nothing Then
    'do something
Else
    'do something else
End If

I kept LookIn:=, LookAt::=, and MatchCase:= to be explicit about what is being matched. The other optional parameters control the order matches are returned in - I'd only specify those if the order is important to my application.

I prefer this, because this is not using selection on the column I'm searching. This can change the some "later logic", may relies on selected cells. — HowToTellAChild, Oct 22 '20 at 22:14

score 5 · Answer 4 · edited Aug 16 '16 at 04:36

Dim strFirstAddress As String
Dim searchlast As Range
Dim search As Range

Set search = ActiveSheet.Range("A1:A100")
Set searchlast = search.Cells(search.Cells.Count)

Set rngFindValue = ActiveSheet.Range("A1:A100").Find(Text, searchlast, xlValues)
If Not rngFindValue Is Nothing Then
  strFirstAddress = rngFindValue.Address
  Do
    Set rngFindValue = search.FindNext(rngFindValue)
  Loop Until rngFindValue.Address = strFirstAddress

How to find a value in an excel column by vba code Cells.Find

4 Answers4

Linked