Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Programming / February 2006

Tip: Looking for answers? Try searching our database.

To find more than one Please

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Steved - 20 Feb 2006 18:30 GMT
Hello from Steved

I've a spreadsheet that has 2 columns of Data

If I type in 003,3.20 that's fine but sometimes there may be more with the
same data, how can the below macro be modified to find the next and so on.
Thankyou.

Sub FindPart()
   Dim res As String, saddr As String
   Dim RgToSearch As Range, RgFound As Range
   Dim secondValue As String
   Set RgToSearch = ActiveSheet.Range("C:C")

   res = Application.InputBox("Type School Number as 001,8.00 to find the
school you are looking for", _
      "Find School", , , , , , 2)
   If res = "False" Then Exit Sub    'exit if Cancel is clicked
   res = Trim(UCase(res))
   If res = "" Then Exit Sub         'exit if no entry and OK is clicked
   If InStr(1, res, ",", vbTextCompare) = 0 Then
      MsgBox "Invalid entry"
      Exit Sub
   End If
   v = Split(res, ",")
   res = Trim(v(LBound(v)))
   secondValue = Trim(v(UBound(v)))
   Set RgFound = RgToSearch.Find(what:=res, _
      LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
    If RgFound Is Nothing Then
      MsgBox "School " & res & " not found."
       Exit Sub
    Else
        saddr = RgFound.Address
        Do
         If RgFound.Offset(0, 1).Text = secondValue Then
          Application.Goto Reference:= _
            RgFound.Offset(0, -1).Address(True, True, xlR1C1)
          Exit Do
         End If
         Set RgFound = RgToSearch.FindNext(RgFound)
        Loop While RgFound.Address <> saddr

   End If
   If RgFound.Offset(0, 1).Text <> secondValue Then
        MsgBox " School Not Found"
   End If
End Sub
ben - 20 Feb 2006 18:38 GMT
VBA help - Search for FindNext, it has a very simple sample there that is
easily modified for your code, I use it a lot.

Signature

When you lose your mind, you free your life.
Ever Notice how we use '' for comments in our posts even if they aren''t
expected to go into the code?

> Hello from Steved
>
[quoted text clipped - 44 lines]
>     End If
>  End Sub
Toppers - 20 Feb 2006 18:51 GMT
Steve,
               The code will find all occurences if you remove the "Exit
Do" statement. What  happens at the GoTo Reference ... do you input data?

Do
         If RgFound.Offset(0, 1).Text = secondValue Then
          Application.Goto Reference:= _
            RgFound.Offset(0, -1).Address(True, True, xlR1C1) '<=== what
happens here?
          Exit Do  <====
         End If

> Hello from Steved
>
[quoted text clipped - 44 lines]
>     End If
>  End Sub
Steved - 20 Feb 2006 20:04 GMT
Hello Toppers from Steved

I type in 001,3.25 to find the row I require.

001 is in Column C and 3.25 is in Column D.

> Steve,
>                 The code will find all occurences if you remove the "Exit
[quoted text clipped - 56 lines]
> >     End If
> >  End Sub
Tom Ogilvy - 20 Feb 2006 18:53 GMT
Sub FindPart()
   Dim res As String, saddr As String
   Dim RgToSearch As Range, RgFound As Range
   Dim secondValue As String
   Set RgToSearch = ActiveSheet.Range("C:C")

   res = Application.InputBox("Type School Number as 001,8.00 to find the
school you are looking for", _
      "Find School", , , , , , 2)
   If res = "False" Then Exit Sub    'exit if Cancel is clicked
   res = Trim(UCase(res))
   If res = "" Then Exit Sub         'exit if no entry and OK is clicked
   If InStr(1, res, ",", vbTextCompare) = 0 Then
      MsgBox "Invalid entry"
      Exit Sub
   End If
   v = Split(res, ",")
   res = Trim(v(LBound(v)))
   secondValue = Trim(v(UBound(v)))
   Set RgFound = RgToSearch.Find(what:=res, _
      LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
    If RgFound Is Nothing Then
      MsgBox "School " & res & " not found."
       Exit Sub
    Else
        saddr = RgFound.Address
        Do
         If RgFound.Offset(0, 1).Text = secondValue Then
          Application.Goto Reference:= _
            RgFound.Offset(0, -1).Address(True, True, xlR1C1)
            ' commenting out the next line should do it
  '        Exit Do
         End If
         Set RgFound = RgToSearch.FindNext(RgFound)
        Loop While RgFound.Address <> saddr

   End If
   If RgFound.Offset(0, 1).Text <> secondValue Then
        MsgBox " School Not Found"
   End If
End Sub

Signature

Regards,
Tom Ogilvy

> Hello from Steved
>
[quoted text clipped - 44 lines]
>     End If
>  End Sub
Steved - 20 Feb 2006 20:11 GMT
Hello from Steved

I've taken out the line Exit Do.

ok now can I have it stopped at the first occurence please as at the moment
it will find the last, something like If I push the F4 it will then find the
next please.

Thankyou.

> Sub FindPart()
>     Dim res As String, saddr As String
[quoted text clipped - 86 lines]
> >     End If
> >  End Sub
Tom Ogilvy - 20 Feb 2006 20:38 GMT
>  at the moment it will find the last
No, it is finding all.  It just doesn't stop until it finds the last.

There is no easy way to pause code execution will you putter around on the
sheet.  What do you actually want to do with each entry that is found?

Do you just want to look at it.   Do you want to enter a value.

It is possible you could combine this with a userform to act as an
interface.

What about just filtering the data so only these rows are visible.  Then you
could do your puttering and run another macro to open them all back up.

If that is satisfactory, then maybe you would consider using the built in
autofilter capability.  If not, then some code could be written.

Signature

Regards,
Tom Ogilvy

> Hello from Steved
>
[quoted text clipped - 96 lines]
> > >     End If
> > >  End Sub
Steved - 20 Feb 2006 21:16 GMT
Hello Tom

Tom the 001 is a School route number and 8.15 is the time.

Yes just to look at them.

In Column A, I have a number which 4537 which is the driver doing that School
I have 8 Bus Depots that use this information so if that can type in
001,8.15 to goto the first then do something to move to the second and so on.

Your answer to filters, yes I thought off doing that but I felt in my mind
that getting a macro to do the finding would suit the staff looking for the
school better.

Thankyou.

Thankyou.

> >  at the moment it will find the last
> No, it is finding all.  It just doesn't stop until it finds the last.
[quoted text clipped - 121 lines]
> > > >     End If
> > > >  End Sub
Tom Ogilvy - 21 Feb 2006 01:13 GMT
Sub FindPart()
   Dim res As String, saddr As String
   Dim RgToSearch As Range, RgFound As Range
   Dim secondValue As String
   Set RgToSearch = ActiveSheet.Range("C:C")

   res = Application.InputBox("Type School Number as 001,8.00 to find the
school you are looking for", _
      "Find School", , , , , , 2)
   If res = "False" Then Exit Sub    'exit if Cancel is clicked
   res = Trim(UCase(res))
   If res = "" Then Exit Sub         'exit if no entry and OK is clicked
   If InStr(1, res, ",", vbTextCompare) = 0 Then
      MsgBox "Invalid entry"
      Exit Sub
   End If
   v = Split(res, ",")
   res = Trim(v(LBound(v)))
   secondValue = Trim(v(UBound(v)))
   Set RgFound = RgToSearch.Find(what:=res, _
      LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
    If RgFound Is Nothing Then
      MsgBox "School " & res & " not found."
       Exit Sub
    Else
        saddr = RgFound.Address
        Do
         If RgFound.Offset(0, 1).Text = secondValue Then
          Application.Goto Reference:= _
            RgFound.Offset(0, -1).Address(True, True, xlR1C1), True
            ' commenting out the next line should do it
            msgbox "Click to Continue Searching"
  '        Exit Do
         End If
         Set RgFound = RgToSearch.FindNext(RgFound)
        Loop While RgFound.Address <> saddr

   End If
   If RgFound.Offset(0, 1).Text <> secondValue Then
        MsgBox " School Not Found"
   End If
End Sub

Signature

Regards,
Tom Ogilvy

> Hello Tom
>
[quoted text clipped - 139 lines]
> > > > >     End If
> > > > >  End Sub
Steved - 21 Feb 2006 18:22 GMT
Hello Tom from Steved

Thankyou very much it works brilliant, once again I thankyou for your time.

Cheers.

> Sub FindPart()
>     Dim res As String, saddr As String
[quoted text clipped - 193 lines]
> > > > > >     End If
> > > > > >  End Sub
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.