Findnext method

**Piyush** · 01-28-2005, 07:22 PM

Here is the portion of the code that im trying to get working. I want in column B to find all occurences of the same part number. But for some reason it doesnt work. Here is the code. I get an error with it. i just cant figure out WHY!. ANy help would be greatly appreciated

Sub Test1()

Dim ptipn As String
Dim myRange As Range
Dim r1 As Range
Dim r2 As Range
'Dim rc As Range
Dim r3 As Range

Dim Reply As Variant

Start:
ptipn = InputBox("Enter PTI Part number: ", "Lookup Value")

Set r1 = Range("B1").EntireColumn
r1.Name = "PN"

Range("PN").Select

Set r2 = Range("PN").Find(ptipn)
If r2 Is Nothing Then

response = MsgBox("The Part number " & _
ptipn & " was not found. Would you like to try again?", vbQuestion + vbYesNo)

If response = vbYes Then
GoTo Start

ElseIf response = vbNo Then
End

End If

Else

r2.Select
Selection.EntireRow.Select
ActiveCell = r2
Set r3 = ActiveCell

differentvendor:
diffvend = MsgBox("Would you like to find another vendor with the same PTI Part number?", vbQuestion + vbYesNo)

If diffvend = vbYes Then

Do While Not IsEmpty(ActiveCell)
MsgBox r3

Range("PN").FindNext(r3).Select

Selection.EntireRow.Select

'If Range("PN").FindNext(r3) Is Nothing Then
'sorry = MsgBox("Sorry, there are no more vendors for that PTI part number. Restart?", vbQuestion + vbYesNo)
' GoTo restart1

GoTo differentvendor

Loop

Else
restart = MsgBox("Restart??", vbQuestion + vbYesNo)
restart1:

If restart = vbYes Then
GoTo Start

Else: End

End If

End If

End If

End Sub

The error is in the findnext line..how do i find the next occurence of the SAME part number??

**firefytr** · 01-28-2005, 07:51 PM

Hi,

This FindNext method works for me ...

Option Explicit

Sub Test1()
    Dim ptipn As String, r1 As Range, r2 As Range, r3 As Range
    Dim response As VbMsgBoxResult, r1Addy As String
Start:
    ptipn = InputBox("Enter PTI Part number:", "Lookup Value")
    If ptipn = vbNullString Then Exit Sub
    Set r1 = Range("B:B")
    Set r2 = r1.Find(ptipn)
    If r2 Is Nothing Then
        response = MsgBox("The Part number " & _
        ptipn & " was not found. Would you like to try again?", vbQuestion + vbYesNo)
        If response = vbYes Then
            GoTo Start
        ElseIf response = vbNo Then
            End
        End If
    Else
        r1Addy = r2.Address
        Set r3 = r2
        Do
            Set r2 = r1.FindNext(r2)
            Set r3 = Union(r3, r2)
        Loop Until r2.Address = r1Addy
    End If
    If Not r3 Is Nothing Then
        'do something with those found here
        r3.Select
    End If
End Sub

HTH

**Piyush** · 01-31-2005, 10:03 AM

thanks fire..ill give it a try. Man urs is soo much more concise..i feel dumb :/

but my first code ever written..so heh..

**Piyush** · 01-31-2005, 11:40 AM

it works..but how do i get the last found part...store the address and compare it to the first initial value..and if its the same...a msgbox displaying "no more matching parts.....want to continue back to the beginning? yes / no?"

**firefytr** · 01-31-2005, 11:57 AM

Yeah, and that's basically how the above works. It will compare the first address it comes across and when the Loop comes back to the starting point (that first address) it will exit the Loop. I guess you could ask to start again, but it would be redundant, as you would have just performed the same commands. So I don't understand the why, but in essence yes, add it right after the loop.

And glad it works for you.

You should have seen my first code!! Oh, was it ugly! LOL!

**Piyush** · 01-31-2005, 06:40 PM

i got another question for u...

i dont know how to call my procedure..

in sheet one..i have Sub Testfile1() and sheet2 i have Sub Testfile2().

Then in the Private Sub Workbook_Open()

i put

Private Sub Workbook_Open()

TestFile1
TestFile2
If Weekday(Now) = vbFriday Then
ThisWorkbook.SaveCopyAs "H:\600 series PB free" & ThisWorkbook.Name
End If

End Sub

It keeps giving me an error that i havent defined sub or procedure. I went to tools >> references but it was dim..so i couldnt select it. I tried to use the CALL Testfile1 and Testfile 2. Same problem. Whats the problem? Please advise