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. :help

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.:dunno


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,

MickDon'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:

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


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