CODEWITHSUNDEEP
vbaexcel
CustomX
CustomX

Reputation: 10113

Excel VBA - Check cell whether it contains piece of text

I want to check a range of cells for a certain piece of text. This text is always in my document, except it's cell is variable (column is always B). So I check a range from 1:75 whether any cells contain a piece of text, but it doesn't seem to work.

Dim FoundRange As Range
Set FoundRange = Cells.Find("5/7 binnen 4h")
Range("I" & EmptyCell + 2).Value = ... (value of cell I on same row as B)

Cell I'm looking for always contains this text Onderhoud 5/7 binnen 4h but its position can vary, that's why I just need to check whether it contains any of it. When I find that cell, I need the value I on the same row.

Any suggestions are welcome!

Upvotes: 3

Views: 128664

Answers (2)

Tim Williams
Tim Williams

Reputation: 166970

This is too much to fit into a comment, so posting it as an answer...

You should be careful when using Find() with just a single argument: if you've previously used Find() in your code and (eg) specified an argument lookat:=xlWhole then you may not get the results you expect, particularly if you're looking for a substring of a cell's value. Settings passed to Find() are persistent: if you don't specify an argument then it may be carried over from the previous use.

As an example (working with a sheet containing the text "hello tom" in B4:

Sub Tester()

    Dim f

    Set f = ActiveSheet.Cells.Find(what:="tom", lookat:=xlPart)
    Report f

    Set f = ActiveSheet.Cells.Find(what:="tom", lookat:=xlWhole)
    Report f

    Set f = ActiveSheet.Cells.Find("tom")
    Report f

End Sub

Sub Report(f)
    If Not f Is Nothing Then
        Debug.Print f.Address
    Else
        Debug.Print "not found"
    End If
End Sub

Running this gives:

$B$4
not found
not found

I seem to recall this is also the case if you've used Find() "manually" and then use it in code later in the same session (didn't test though).

Upvotes: 3

Steve Homer
Steve Homer

Reputation: 3932

Could you not just search for the substring?

Sheet1.Cells.Find("string to find")

Will return the a range containing the string (or nothing if the string can't be found.

For example

Public Sub Macro1()
Dim FoundRange As Range

Set FoundRange = Sheet1.Cells.Find("5/7 binnen 4h")

' display the cell address to the user
MsgBox FoundRange.Address


' put the found value in column i in the same row as the found text in a known location ($C$1 in this case)
Sheet1.Range("$C$1").Value = Sheet1.Cells(FoundRange.Row, 9).Value

' put the found value in four columns to the right in the same row as the found text in a known location ($C$1 in this case)
Sheet1.Range("$C$2").Value = FoundRange.Offset(0, 4).Value

End Sub

Upvotes: 8

Related Questions