View Full Version : [SOLVED:] FIND function in a macro
mp_robinson_uk
12-07-2004, 07:03 AM
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
12-07-2004, 07:24 AM
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
12-07-2004, 07:30 AM
i would say you need:
IsError(Cells.Find... etc
on the two lines in your loop
Jacob Hilderbrand
12-07-2004, 07:43 AM
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
12-07-2004, 07:45 AM
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
12-07-2004, 08:13 AM
Hi Jacob,
Thanks once again! That worked perfectly!
Thanks for all your help!
Thanks Aaron and Killian,
Mick
Jacob Hilderbrand
12-07-2004, 08:21 AM
You're Welcome :)
Take Care
Aaron Blood
12-07-2004, 08:32 AM
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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.