Find Next problem

[Terry K]

Hello all,
Thank you for taking the time to look at this for me. I am a little bit
of a rookie at this and trying to learn as I continue.
Here is some code that I am working on and having an extemely hard time
in making it work.
I am having two probelms with it. First if the string that I am looking
for is in a1 it does not seem to find it. Next is the findnext routine,
it generates runtime error 1004 "Unable to get the findnext property of
the range class. I think that I have been matching what I have been
reading on this site?? Something misunderstood?
Any help would be appreciated. tia.
Terry
Sub find_files()
Dim filter As Variant
Dim wbk As Workbook, sh As Worksheet
Dim i As Single, rng As Range
Dim firstcell As String
Dim caption As String
Dim selectedfile As Variant
filter = "Excel files (*.xls), *.xls"
caption = "Select a File"
selectedfile = Application.GetOpenFilename(filter, , caption, _
, True)
Select Case IsArray(selectedfile)
Case True
For i = LBound(selectedfile) To UBound(selectedfile)
Set wbk = Workbooks.Open(selectedfile(i))
For Each sh In wbk.Worksheets
Set rng = Cells.Find(UserForm1.TextBox2.Text, _
LookIn:=xlValues, lookat:=xlWhole,
MatchCase:=False)
rng.Activate
Do
If Not rng Is Nothing Then
firstcell = rng.Address
MsgBox "Found " & UserForm1.TextBox2.Text & " in "
_
& wbk.Name & " on Sheet " & sh.Name & " in cell " &
rng.Address & vbCr & vbLf & "Continue?", vbYesNo, "Found your Text"
End If
Set rng = rng.FindNext(after:=firstcell).Activate
<<<<<<problem here>>>>>>
Loop Until ActiveCell.Address = firstcell
Next sh
wbk.Close (False)
Next i
Case False
MsgBox ("No Files Selected")
End Select

End Sub

[Dave Peterson]

This seems to work ok for me:

Option Explicit

Sub find_files()
Dim filter As Variant
Dim wbk As Workbook, sh As Worksheet
Dim i As Single, rng As Range
Dim firstcell As String
Dim caption As String
Dim selectedfile As Variant
Dim Resp As Long

filter = "Excel files (*.xls), *.xls"
caption = "Select a File"
selectedfile = Application.GetOpenFilename(filter, , caption, , True)
Select Case IsArray(selectedfile)
Case True
For i = LBound(selectedfile) To UBound(selectedfile)
Set wbk = Workbooks.Open(selectedfile(i))
For Each sh In wbk.Worksheets
sh.Select 'if you want to activate the found cell
Set rng = sh.Cells.Find(UserForm1.TextBox2.Text, _
after:=sh.Cells(sh.Cells.Count), LookIn:=xlValues, _
lookat:=xlWhole, MatchCase:=False)

If Not rng Is Nothing Then
firstcell = rng.Address
rng.Activate
Do
Resp = MsgBox("Found " & UserForm1.TextBox2.Text _
& " in " & wbk.Name & " on Sheet " _
& sh.Name & " in cell " & rng.Address & vbCr _
& vbLf & "Continue?", vbYesNo, _
"Found your Text")
If Resp = vbNo Then
Exit Sub 'just stop???
Else
Set rng = sh.Cells.FindNext(after:=rng)
End If

Loop Until rng.Address = firstcell _
Or rng Is Nothing
End If
Next sh
wbk.Close (False)
Next i
Case False
MsgBox "No Files Selected"
End Select

End Sub

Terry K wrote:
>
> Hello all,
> Thank you for taking the time to look at this for me. I am a little bit
> of a rookie at this and trying to learn as I continue.
> Here is some code that I am working on and having an extemely hard time
> in making it work.
> I am having two probelms with it. First if the string that I am looking
> for is in a1 it does not seem to find it. Next is the findnext routine,
> it generates runtime error 1004 "Unable to get the findnext property of
> the range class. I think that I have been matching what I have been
> reading on this site?? Something misunderstood?
> Any help would be appreciated. tia.
> Terry
> Sub find_files()
> Dim filter As Variant
> Dim wbk As Workbook, sh As Worksheet
> Dim i As Single, rng As Range
> Dim firstcell As String
> Dim caption As String
> Dim selectedfile As Variant
> filter = "Excel files (*.xls), *.xls"
> caption = "Select a File"
> selectedfile = Application.GetOpenFilename(filter, , caption, _
> , True)
> Select Case IsArray(selectedfile)
> Case True
> For i = LBound(selectedfile) To UBound(selectedfile)
> Set wbk = Workbooks.Open(selectedfile(i))
> For Each sh In wbk.Worksheets
> Set rng = Cells.Find(UserForm1.TextBox2.Text, _
> LookIn:=xlValues, lookat:=xlWhole,
> MatchCase:=False)
> rng.Activate
> Do
> If Not rng Is Nothing Then
> firstcell = rng.Address
> MsgBox "Found " & UserForm1.TextBox2.Text & " in "
> _
> & wbk.Name & " on Sheet " & sh.Name & " in cell " &
> rng.Address & vbCr & vbLf & "Continue?", vbYesNo, "Found your Text"
> End If
> Set rng = rng.FindNext(after:=firstcell).Activate
> <<<<<<problem here>>>>>>
> Loop Until ActiveCell.Address = firstcell
> Next sh
> wbk.Close (False)
> Next i
> Case False
> MsgBox ("No Files Selected")
> End Select
>
> End Sub

--

Dave Peterson

[Dave Peterson]

You can change this portion:

Loop Until rng.Address = firstcell _
Or rng Is Nothing

to:

Loop Until rng.Address = firstcell

You don't need to check for nothingness.

Dave Peterson wrote:
>
> This seems to work ok for me:
>
> Option Explicit
>
> Sub find_files()
> Dim filter As Variant
> Dim wbk As Workbook, sh As Worksheet
> Dim i As Single, rng As Range
> Dim firstcell As String
> Dim caption As String
> Dim selectedfile As Variant
> Dim Resp As Long
>
> filter = "Excel files (*.xls), *.xls"
> caption = "Select a File"
> selectedfile = Application.GetOpenFilename(filter, , caption, , True)
> Select Case IsArray(selectedfile)
> Case True
> For i = LBound(selectedfile) To UBound(selectedfile)
> Set wbk = Workbooks.Open(selectedfile(i))
> For Each sh In wbk.Worksheets
> sh.Select 'if you want to activate the found cell
> Set rng = sh.Cells.Find(UserForm1.TextBox2.Text, _
> after:=sh.Cells(sh.Cells.Count), LookIn:=xlValues, _
> lookat:=xlWhole, MatchCase:=False)
>
> If Not rng Is Nothing Then
> firstcell = rng.Address
> rng.Activate
> Do
> Resp = MsgBox("Found " & UserForm1.TextBox2.Text _
> & " in " & wbk.Name & " on Sheet " _
> & sh.Name & " in cell " & rng.Address & vbCr _
> & vbLf & "Continue?", vbYesNo, _
> "Found your Text")
> If Resp = vbNo Then
> Exit Sub 'just stop???
> Else
> Set rng = sh.Cells.FindNext(after:=rng)
> End If
>
> Loop Until rng.Address = firstcell _
> Or rng Is Nothing
> End If
> Next sh
> wbk.Close (False)
> Next i
> Case False
> MsgBox "No Files Selected"
> End Select
>
> End Sub
>
> Terry K wrote:
> >
> > Hello all,
> > Thank you for taking the time to look at this for me. I am a little bit
> > of a rookie at this and trying to learn as I continue.
> > Here is some code that I am working on and having an extemely hard time
> > in making it work.
> > I am having two probelms with it. First if the string that I am looking
> > for is in a1 it does not seem to find it. Next is the findnext routine,
> > it generates runtime error 1004 "Unable to get the findnext property of
> > the range class. I think that I have been matching what I have been
> > reading on this site?? Something misunderstood?
> > Any help would be appreciated. tia.
> > Terry
> > Sub find_files()
> > Dim filter As Variant
> > Dim wbk As Workbook, sh As Worksheet
> > Dim i As Single, rng As Range
> > Dim firstcell As String
> > Dim caption As String
> > Dim selectedfile As Variant
> > filter = "Excel files (*.xls), *.xls"
> > caption = "Select a File"
> > selectedfile = Application.GetOpenFilename(filter, , caption, _
> > , True)
> > Select Case IsArray(selectedfile)
> > Case True
> > For i = LBound(selectedfile) To UBound(selectedfile)
> > Set wbk = Workbooks.Open(selectedfile(i))
> > For Each sh In wbk.Worksheets
> > Set rng = Cells.Find(UserForm1.TextBox2.Text, _
> > LookIn:=xlValues, lookat:=xlWhole,
> > MatchCase:=False)
> > rng.Activate
> > Do
> > If Not rng Is Nothing Then
> > firstcell = rng.Address
> > MsgBox "Found " & UserForm1.TextBox2.Text & " in "
> > _
> > & wbk.Name & " on Sheet " & sh.Name & " in cell " &
> > rng.Address & vbCr & vbLf & "Continue?", vbYesNo, "Found your Text"
> > End If
> > Set rng = rng.FindNext(after:=firstcell).Activate
> > <<<<<<problem here>>>>>>
> > Loop Until ActiveCell.Address = firstcell
> > Next sh
> > wbk.Close (False)
> > Next i
> > Case False
> > MsgBox ("No Files Selected")
> > End Select
> >
> > End Sub
>
> --
>
> Dave Peterson

--

Dave Peterson

[Bruno Campanini]

"Terry K" <[email protected]> wrote in message
news:[email protected]...
> Hello all,
> Thank you for taking the time to look at this for me. I am a little bit
> of a rookie at this and trying to learn as I continue.
> Here is some code that I am working on and having an extemely hard time
> in making it work.
> I am having two probelms with it. First if the string that I am looking
> for is in a1 it does not seem to find it.

When the string you are looking for is located in the first
cell of your range, Find finds it as last.
That's the Find's AI!
If you want the first cell searched first you must tell Find
you want to start After the last cell:
.Find("SearchString", Ra1.End(xlDown))
where Ra1.End(xlDown) is the last cell of your range.

Try this to see how Find loops:
============================
Dim CellFound As Range, Ra1 As Range
Dim FirstAddress As String, j as Long

With Ra1
Set CellFound = .Find("SearchString", .End(XlDown))
If Not CellFound Is Nothing Then
FirstAddress = CellFound.Address
Do
j = j +1
CellFound.Select
MsgBox "Found: " & j
Set CellFound = .FindNext(CellFound)
Loop While Not CellFound Is Nothing And _
CellFound.Address <> FirstAddress
End If
End With
==========================
Ciao
Bruno

[Tom Ogilvy]

That would be true if the range is completely filled. Otherwise, it would
not.

Set RA1 = Range("A1:A20")
? ra1.End(xldown).Address
$A$2

Better would be RA1(RA1.count)

With Ra1
Set CellFound = .Find("SearchString", Ra1(Ra1.count))
If Not CellFound Is Nothing Then

For best performance, it would be useful to use some of the other arguments
for the Find method documented in Help.
--
Regards,
Tom Ogilvy



"Bruno Campanini" <[email protected]> wrote in message
news:[email protected]...
> "Terry K" <[email protected]> wrote in message
> news:[email protected]...
> > Hello all,
> > Thank you for taking the time to look at this for me. I am a little bit
> > of a rookie at this and trying to learn as I continue.
> > Here is some code that I am working on and having an extemely hard time
> > in making it work.
> > I am having two probelms with it. First if the string that I am looking
> > for is in a1 it does not seem to find it.
>
> When the string you are looking for is located in the first
> cell of your range, Find finds it as last.
> That's the Find's AI!
> If you want the first cell searched first you must tell Find
> you want to start After the last cell:
> .Find("SearchString", Ra1.End(xlDown))
> where Ra1.End(xlDown) is the last cell of your range.
>
> Try this to see how Find loops:
> ============================
> Dim CellFound As Range, Ra1 As Range
> Dim FirstAddress As String, j as Long
>
> With Ra1
> Set CellFound = .Find("SearchString", .End(XlDown))
> If Not CellFound Is Nothing Then
> FirstAddress = CellFound.Address
> Do
> j = j +1
> CellFound.Select
> MsgBox "Found: " & j
> Set CellFound = .FindNext(CellFound)
> Loop While Not CellFound Is Nothing And _
> CellFound.Address <> FirstAddress
> End If
> End With
> ==========================
> Ciao
> Bruno
>
>

[Bruno Campanini]

"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> That would be true if the range is completely filled. Otherwise, it would
> not.
>
> Set RA1 = Range("A1:A20")
> ? ra1.End(xldown).Address
> $A$2
>
> Better would be RA1(RA1.count)

Yes of course. Every time you use .End(xlDown) it is implied
all the range is completely filled.
In any case the last cell of range must be used.
This can be [A20] or, as you suggest, Ra1(Ra1.Count)

> For best performance, it would be useful to use some of the other
> arguments
> for the Find method documented in Help.

Sure, but only if you need for those arguments values
different from the default ones.
Otherwise there is no difference in performance.

Ciao Tom
Bruno

[Tom Ogilvy]

That is the point. Several of the arguments are persistent - there are no
defaults per se - so if you are looking for a value that is dependent on a
persistent setting (such as xlPart vice xlWhole), you don't know what the
current setting is - that is why it is always best to set them explicitly.
(but the OP appears to be doing that anyway)

--
Regards,
Tom Ogilvy


"Bruno Campanini" <[email protected]> wrote in message
news:[email protected]...
> "Tom Ogilvy" <[email protected]> wrote in message
> news:[email protected]...
> > That would be true if the range is completely filled. Otherwise, it
would
> > not.
> >
> > Set RA1 = Range("A1:A20")
> > ? ra1.End(xldown).Address
> > $A$2
> >
> > Better would be RA1(RA1.count)
>
> Yes of course. Every time you use .End(xlDown) it is implied
> all the range is completely filled.
> In any case the last cell of range must be used.
> This can be [A20] or, as you suggest, Ra1(Ra1.Count)
>
> > For best performance, it would be useful to use some of the other
> > arguments
> > for the Find method documented in Help.
>
> Sure, but only if you need for those arguments values
> different from the default ones.
> Otherwise there is no difference in performance.
>
> Ciao Tom
> Bruno
>
>

[Bruno Campanini]

"Tom Ogilvy" <[email protected]> wrote in message
news:%[email protected]...
> That is the point. Several of the arguments are persistent - there are no
> defaults per se - so if you are looking for a value that is dependent on a
> persistent setting (such as xlPart vice xlWhole), you don't know what the
> current setting is - that is why it is always best to set them explicitly.
> (but the OP appears to be doing that anyway)

Ok Tom.
Now it's perfectly clear what you mean.
And you are perfectly right.

Ciao
Bruno

[Terry K]

Thank you all very much for your help. Here is what I finished up with.
It is not as professional as perhaps it should be but it does seem to
get the job done. I can spend a little time in the future and finish it
up.
Once again thank you all for your time, it is much appreciated.
Terry

Option Explicit

Sub find_files()
Dim filter As Variant
Dim wbk As Workbook, sh As Worksheet
Dim saddr As String
Dim i As Single, rng As Range
Dim firstcell As String
Dim caption As String
Dim ans As String
Dim selectedfile As Variant
filter = "Excel files (*.xls), *.xls"
caption = "Select a File"
selectedfile = Application.GetOpenFilename(filter, , caption, _
, True)
Select Case IsArray(selectedfile)
Case True
For i = LBound(selectedfile) To UBound(selectedfile)
Set wbk = Workbooks.Open(selectedfile(i))
For Each sh In wbk.Worksheets
sh.Activate
Set rng = sh.Cells.Find(UserForm1.TextBox2.Text, _
LookIn:=xlValues, lookat:=xlPart, MatchCase:=False)
If Not rng Is Nothing Then
saddr = rng.Address
Do
Application.Visible = True
Range(rng.Address).Select
ans = MsgBox("Found " & UserForm1.TextBox2.Text & "
in " _
& wbk.Name & " on Sheet " & sh.Name & " in cell " &
rng.Address & vbCr & vbLf & _
vbLf & "Continue?", vbYesNo, "Found your Text")
If ans = vbNo Then Exit Sub
Set rng = sh.Cells.FindNext(rng)
Loop While rng.Address <> saddr
End If
Next sh
wbk.Close (False)
Next i
MsgBox "All done now", vbOKOnly
Application.Visible = True
Case False
MsgBox ("No Files Selected")
Application.Visible = True
End Select

End Sub