Findnext method

[Piyush]

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]

Hi,


This FindNext method works for me ...

Please Login or Register  to view this content.

HTH

[Piyush]

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]

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]

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]

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

[firefytr]

Right off hand, I'd say take your two Sub routines (Testfile1 and Testfile2) and put them into a Standard Module ( Insert | Module ), not in a worksheet module. And without seeing your Testfile1 and/or Testfile2 routines, I'm not sure what else it may be. But definitely put them into a standard module first.

Also, is that your entire filename? You are appending prior to the name, saving to H? Which I'm assuming is a network drive.. ? If that is the directory, don't forget your last backslash.

[Piyush]

im kinda confused with modules vs procedures...


http://www.excelforum.com/newreply.p...ote=1&p=869479

check it out...

does my reasoning make sense??

well what i said in that one was....if i put testfile1 and testfile2 into the individual sheets for code...why should it need to be placed into the module. It would only make sense if i was altering the workbook 'overall'. Since im only throwing data into the respective sheets. All i want is to load both the routines....

i ask this because if i ever decide to make a bigger program...i dont awnna copy loads of routines into modules....

can u give me an example as to when u would EVER use whats in the sheets procedures??

sorry.....im still in college...heh..

[firefytr]

Hey, no problem. We're all learning, all the time.

There are a few types of routine 'containers'. Here are some and what they are generally used for (note: these are general and usual circumstances - not always):

Standard Modules:
Usually this is your typical Sub Routines. These are usually non-sheet specific and called from anywhere within the workbook. You see a lot of these assigned to buttons and/or various drawing objects. When user's click on these Objects, the Routine will run. These are not event-specific procedures. These routines can be declared as Public or Private, depending on the specific needs.


Worksheet Modules:
These hold worksheet level event code. A worksheet level event can be anything from a SelectionChange, cell Change, sheet Activate/Deactivate, Calculate, etc. But they are all events associated only with the sheet that the code is located. So each sheet will have it's own module. So for one worksheet, there is the Object (the actual sheet) and the Code (worksheet module). Generally these routines are declared as private.


ThisWorkbook Module:
These are the same concept as the worksheet modules, but are related to workbook level events. Some examples would include Workbook_Open, BeforeClose, BeforeRightClick, BeforeDblClick, WindowActivate, WindowDeactivate, WindowResize, Before/AfterXmlImport/Export (2003 only), etc. The most common are the Workbook_Open and Workbook_BeforeClose events. Again, these are events, a user cannot call these events on a whim, the event must take place before the code will be compiled.


Class Modules:
These are very special and could (and have) take an entire book to describe them. John Walkenbach has some good examples on his site. They can be very detremental if you don't know what you are doing; so I sugget a very large amount of reading on these before you try testing them out.


This is a very, very broad overview of the different module types. I'd suggest a Google group search on this subject. There are some good postings over at MrExcel.com also on the differences along with various uses and how they can interact with each other.


HTH

[Piyush]

Very structured. Kind of like the army...u have captains.....that do what they need to..then u have soldiers and every soldier does his own thing. What captains say....soldiers may or may not do depending on the "instruction"....

Could you explain to me exactly what objects are?? I have run into that a billion times. And i still dont get it .. checked on the internet for like 30 minutes yesterday...nothing great.


Also....how private are private subs procedures?? if in a worksheet a procedure is declared private, who can access it? no one?? thanks a lot

[firefytr]

Yes, it is very structured. The Army is a very good analogy, as they have a very structured chain of command with very (usually) definite prodecures and policies. We call that Unity of Command/Chain of Command. Very much the same, yes.

Regarding Private and Public type procedures:

Think of them as to who ownership belongs to; who can call it and where it can be called from.

Private:
The routine, function or variable can ONLY be called or run from within that module. Whether it be a worksheet module, standard module, etc.

Public:
A routine, function or variable that can be called from anywhere within the current project. There is no need to redelcare or redefine it. This is helpful if you want to use the same variable in more than one location. Also, probably the best use imho, is to create a UDF (User Defined Function) that can be used *globally* that has many uses.

A great example of this is a function that will tell me if a sheet exists or not ..

Please Login or Register  to view this content.

And to use such a function in a routine, you can use something like this ...

Please Login or Register  to view this content.

There are many uses for a function that tests whether a sheet exists or not. You can do this with pretty much anything you wanted, limited only by your imagination.

Some variables that you wish to be *global* would look something like this ...

Please Login or Register  to view this content.

This can be placed in a Standard Module, generally at the top and outside of any other procedure/sub. Now you can use the variable MYVARIABLE (which means "My String Variable") anywhere in the project.


Now, as for what an Object is, that's a little more in-depth. Let me start by giving you some examples of Objects:

A Worksheet.
A Chart.
A workbook.
An Application.
A Range
A shape (autoshap, square, line, rectangle, freeform, etc.)
Etc, etc.

Objects have properties. You can set these properties from variables, or set these properties to variables. One example is the Name property. Here's an example (all in a standard module) ...

Please Login or Register  to view this content.

But in essense, Objects have properties and are NOT methods. You will see all of those referred to in the VBE and/or Help files. If you want a much better idea, check out the Object Model in VBA. While in the VBE (Visual Basic Editor: Alt + F11) press F2. This will give you a good idea of properties, methods, objects, etc. The MSDN website has some good information also.


HTH