PDA

View Full Version : [SOLVED] Solved: Find function



Paleo
03-24-2005, 11:38 AM
Hi guys,

I am using this Find code that I got from a KN entry writen by John (johnske):



Option Explicit
'<< A SIMPLIFICATION OF THE MS "FIND" FUNCTION >>

Sub MSFindIt()
Dim Cell As Range, FirstAddress As String
With Range("A1:D500")
Set Cell = .Find("it", LookIn:=xlValues, searchorder:=xlByRows, _
LookAt:=xlPart, MatchCase:=True)
On Error Goto Finish '<< the "error" being that there are no "it"s
FirstAddress = Cell.Address '<< bookmark the start-point of the search
Do
'Do whatever you want below, the messagebox is just an example...
MsgBox "An ''it'' was found at " & Cell.Address & _
" (" & Cell.Value & ")" Set Cell = .FindNext(Cell)
Loop Until Cell Is Nothing Or Cell.Address = FirstAddress
End With
Finish:
End Sub


My problem is that I need to set a variable to a certain value when an error arises and keep on running the macro, so the "On Error Goto ..." thing doesnt help me, I need something different.

Here is my code:


For i = 2 To Range("A65536").End(xlUp).Row
opVlr = Range("D3")
Dim Cell As Range, Ender As String , Ender2 As String, strCl As String
With Range("A2:A" & Sheets("Test").Range("A65536").End(xlUp).Row)
Set Cell = .Find(opVlr, LookIn:=xlValues, searchorder:=xlByRows, _
LookAt:=xlPart, MatchCase:=True)
On Error Goto Finish
Finish:
strCl = "Nada"
FirstAddress = Cell.Address(False, False)
FirstAddress2 = Right(FirstAddress, Len(FirstAddress) -1)
End With
... ' here the code goes and goes


I simply need to have that strCl value to work with if an error has happened or in other words, IF the find didnt find a thing.

Any suggestions?

Ken Puls
03-24-2005, 11:55 AM
Hi Carlos,

Didn't see the need for the loop, but I haven't used Find much, so this might not be suitable.

Try this though:


Option Explicit
Option Compare Text

Sub test()
Dim Cell As Range
Dim firstaddress As String, firstaddress2 As String
Dim opVlr As String, i As Long
opVlr = Range("D3")
With Range("A2:A" & Sheets("Test").Range("A65536").End(xlUp).Row)
Set Cell = .Find(opVlr, LookIn:=xlValues, searchorder:=xlByRows, _
LookAt:=xlPart, MatchCase:=True)
End With
On Error Resume Next
firstaddress = Cell.Address(False, False)
firstaddress2 = Right(firstaddress, Len(firstaddress) - 1)
If Err.Number <> 0 Then firstaddress = "Not found"
MsgBox firstaddress
On Error GoTo 0
End Sub

Jacob Hilderbrand
03-24-2005, 11:55 AM
If Cell Is Nothing Then
'Not Found
Else
'Found
End If

mdmackillop
03-24-2005, 11:55 AM
How about


Sub DoFind()
For i = 2 To Range("A65536").End(xlUp).Row
opVlr = Range("D3")
Dim Cell As Range, Ender As String, Ender2 As String, strCl As String
With Range("A2:A" & Sheets("Test").Range("A65536").End(xlUp).Row)
Set Cell = .Find(opVlr, LookIn:=xlValues, searchorder:=xlByRows, _
LookAt:=xlPart, MatchCase:=True)
If Cell Is Nothing Then
strCl = "Nada"
Else
FirstAddress = Cell.Address(False, False)
FirstAddress2 = Right(FirstAddress, Len(FirstAddress) - 1)
End If
End With
j = j + 1
Next
MsgBox j
End Sub



The msgbox is just to show something ran!

Ken Puls
03-24-2005, 12:00 PM
Geese! You'd think this guy gets special treatment with 3 answers posted at exactly the same time!

:rotlaugh:

mdmackillop
03-24-2005, 12:02 PM
Zack will be along any minute!

Paleo
03-24-2005, 12:03 PM
Hi guys,

great thanks!!

Ken: Yes, you right, in that fragment of code the loop is unnecessary but its used some lines ahead.

But as t would incur in error when the find founds nothing I must use a "On Error Resume Next", right?

Something like this on Malcolms code:


Sub DoFind()
For i = 2 To Range("A65536").End(xlUp).Row
opVlr = Range("D3")
Dim Cell As Range, Ender As String, Ender2 As String, strCl As String
With Range("A2:A" & Sheets("Test").Range("A65536").End(xlUp).Row)
Set Cell = .Find(opVlr, LookIn:=xlValues, searchorder:=xlByRows, _
LookAt:=xlPart, MatchCase:=True)
On Error Resume Next
If Cell Is Nothing Then
strCl = "Nada"
Else
FirstAddress = Cell.Address(False, False)
FirstAddress2 = Right(FirstAddress, Len(FirstAddress) - 1)
End If
On Error Goto 0
End With
j = j + 1
Next
MsgBox j
End Sub

Ken Puls
03-24-2005, 12:12 PM
Ken: Yes, you right, in that fragment of code the loop is unnecessary but its used some lines ahead.

But as t would incur in error when the find founds nothing I must use a "On Error Resume Next", right?

Fair enough. What I gave you actually has that, but I assign the range (or lack of) to a variable and test that, while Jake and Malcolm skipped that step and just tested the cell immediately. Either should work, but their approach is more efficient. :yes

mdmackillop
03-24-2005, 12:13 PM
Hi Carlos,
Finding nothing is not an error, although trying to manipulate something that is nothing probably is!

Paleo
03-24-2005, 12:15 PM
Hi Ken,

yes I saw that on yours :thumb , thanks!

Hi Malcolm,

yes, thats why I asked about putting that error handling in it.


Hi Carlos,
Finding nothing is not an error, although trying to manipulate something that is nothing probably is!

Zack Barresse
03-24-2005, 12:16 PM
Zack will be along any minute!

Yupper!! :D


Carlos, if we look at ..


Sub MSFindIt()
Dim Cell As Range, FirstAddress As String
With Range("A1:D500")
Set Cell = .Find("it", LookIn:=xlValues, searchorder:=xlByRows, _
LookAt:=xlPart, MatchCase:=True)
On Error GoTo Finish
FirstAddress = Cell.Address
Do
MsgBox "An ""it"" was found at " & Cell.Address & _
" (" & Cell.Value & ")"
Set Cell = .FindNext(Cell)
Loop Until Cell Is Nothing Or Cell.Address = FirstAddress
End With
Finish:
End Sub


.. specifically these two lines ..



On Error GoTo Finish
FirstAddress = Cell.Address

Jake is right, Cell variable will be Nothing, so Cell.Address will present your error. From here it will go to the predefined location of Finish.

I am currently unsure of what you need to have your variable assigned for upon error. What is it exactly that you need the variable for?

Paleo
03-24-2005, 12:23 PM
That makes me very happy, Ken. Thanks guys!!


Geese! You'd think this guy gets special treatment with 3 answers posted at exactly the same time!

:rotlaugh:

Hi Zack,

I need to assign a string to a variable if nothing is found, so I did this.



Sub DoFind()
For i = 2 To Range("A65536").End(xlUp).Row
opVlr = Range("D3")
Dim Cell As Range, Ender As String, Ender2 As String, strCl As String
With Range("A2:A" & Sheets("Test").Range("A65536").End(xlUp).Row)
Set Cell = .Find(opVlr, LookIn:=xlValues, searchorder:=xlByRows, _
LookAt:=xlPart, MatchCase:=True)
On Error Resume Next
If Cell Is Nothing Then strCl = "Nada"
FirstAddress = Cell.Address(False, False)
FirstAddress2 = Right(FirstAddress, Len(FirstAddress) - 1)
On Error Goto 0
End With
Next
End Sub

Ken Puls
03-24-2005, 12:28 PM
Hey Carlos,

Get this:


opVlr = Range("D3")
out of the loop. You shouldn't need to reassign it every iteration, I don't think.

Paleo
03-24-2005, 12:30 PM
Hi Ken,

yes, you right, I miss typed this :banghead: , the right would be:


opVlr = Range("D" & i)

Zack Barresse
03-24-2005, 12:33 PM
Hey Carlos,

Get this:


opVlr = Range("D3")
out of the loop. You shouldn't need to reassign it every iteration, I don't think.

Also the Dim statements. And you can get rid of the On Error statements if your using the If Cell Is Nothing check ...


Sub DoFind()
Dim Cell As Range, Ender As String, Ender2 As String, strCl As String, opVlr As String
For i = 2 To Range("A65536").End(xlUp).Row
opVlr = Range("D" & i).Value
With Range("A2:A" & Sheets("Test").Range("A65536").End(xlUp).Row)
Set Cell = .Find(opVlr, LookAt:=xlPart, MatchCase:=True)
If Cell Is Nothing Then
strCl = "Nada"
Else
FirstAddress = Cell.Address(False, False)
FirstAddress2 = Right(FirstAddress, Len(FirstAddress) - 1)
Set Cell = Nothing
End If
End With
Next
End Sub

.. their only compiled at run time anyway.


EDIT**: Added the last Set Cell = Nothing check before the next iteration. You must have this!

Paleo
03-24-2005, 12:37 PM
Great,

thanks Zack, Ken, Malcolm, Jake and John (for the starting code), you great guys!

I am marking it solved now!