.Find data that is result of formula?

[jmpatrick]

Hello again. Today I'm trying to match data in column 5, which is the result of a formula in a cell.

Here's my code:

    With Sheets("Models")
    .Columns(5).Find(what:=x, lookat:=xlWhole).EntireRow.Delete
    a = .Range("a2", .Range("a65536").End(xlUp)).Value
    End With
    Me.ModelName.List = a
    End If

...and here's the formula in the column 5 cell:

=IF(C1=2,B1&" "&"Duplex",IF(AND(C1>=3,C1<="6"),B1&" "&C1&"-Unit",B1))

If E1 for example is Abbeyville, and the value in Me.ModelName.List is also Abbeyville my code above will not find it and delete the row.

Make sense?

 

[GWteB]

The Find method needs some more parameters to give you the expected result. As a matter of fact, each time you use the Find method, the settings for LookIn, LookAt and SearchOrder are saved.
So if you're looking for values, to be sure you better use a line of code like

.Columns(5).Find(What:=x, LookIn:=xlValues, Lookat:=xlWhole)

In addition, if the What is found, the result will be the first occurrence and if nothing is found your code errors.

 

[jmpatrick]

The Find method needs some more parameters to give you the expected result. As a matter of fact, each time you use the Find method, the settings for LookIn, LookAt and SearchOrder are saved.
So if you're looking for values, to be sure you better use a line of code like

.Columns(5).Find(What:=x, LookIn:=xlValues, Lookat:=xlWhole)

In addition, if the What is found, the result will be the first occurrence and if nothing is found your code errors.

Works great! Thanks.

 

[GWteB]

You're welcome and thanks for the feedback.