FIND function in a macro

[mp_robinson_uk]

Hi,

I am trying to write a macro that deletes a row if either the C or D column in that row do not have a / as part of the text.

I tried the following but got an error that the FIND sub or function is not defined. Why? I thought FIND was a built in Function.

Thanks in advance for your help!

Mick

Sub pedro_remove_ios()
Dim LastRow As Long
    Dim Counter         As Integer
LastRow = Cells.Find(What:="*", LookIn:=xlValues, _
    SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
   Counter = LastRow - 4
   Do While Counter > 1
     If IsError(Find("/", Cells(Counter & "C"))) _
       Or IsError(Find("/", Cells(Counter & "D"))) Then
       Cells(Counter, "A").EntireRow.Delete
     End If
     Counter = Counter - 1
   Loop
End Sub

[Aaron Blood]

I believe FIND is a method/property of a range object.


In your code where you try to use it independently:

If IsError(Find("/", Cells(Counter & "C")))

It fails...

[Killian]

i would say you need:
IsError(Cells.Find... etc
on the two lines in your loop

[Jacob Hilderbrand]

Cells.Find

is used to search a worksheet for a specific value. If you want to use Find on a specific cell you can use the following.

Application.Worksheetfunction.Find

however, VBA has a function of its own that you can use the following.

If InStr(1, Range("C" & Counter).Text, "/") > 0 Then

InStr will be 0 if the string is not found. This means that there is no need for error handling as would be the case with Find (or Search).

Also to use Cells to specify a range the following is the correct syntact to use:

Cells(RowNumber, ColumnNumber)

[mp_robinson_uk]

Hi Killian,

If I try Cells.Find I get a type mismatch on that line.

Sub pedro_remove_ios()
Dim LastRow As Long
    Dim Counter         As Integer
LastRow = Cells.Find(What:="*", LookIn:=xlValues, _
    SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1
    Counter = LastRow - 4
    Do While Counter > 1
        If IsError(Cells.Find("/", Cells(Counter & "C"))) _
        Or IsError(Cells.Find("/", Cells(Counter & "D"))) Then
        Cells(Counter, "A").EntireRow.Delete
    End If
    Counter = Counter - 1
Loop
End Sub

Hi Aaron,

I know I can use the function

=IsError(Find("/", A1))

in a Cell within xl itself and that works.

Regards,

Mick

[mp_robinson_uk]

Hi Jacob,

Thanks once again! That worked perfectly!

Thanks for all your help!

Thanks Aaron and Killian,

Mick

[Jacob Hilderbrand]

You're Welcome

Take Care

[Aaron Blood]

Hi Killian,

Hi Aaron,

I know I can use the function =IsError(Find("/", A1)) in a Cell within xl itself and that works.

Regards,

Mick

Don't confuse the VBA Find method with the Find worksheet function. Just remember if you want to use a worksheet function in VBA you need to use the following syntax:

[VBA] Set Fn = Application.WorksheetFunction
x = Fn.Find("/", Range("A1"))
[/VBA]

Also, as was pointed out above, INSTR is the VBA equivalent to the FIND worksheet function.