Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using 'Find' and 'FindNext' in vba
I have been struggling to write a short piece of code to do the following
Sheets("Invoices").Range("a:a") contains Supplier Names Sheets("Invoices").Range("c:c") contains Invoice Numbers I have created a userform to to display the invoice details after specifying the above details i.e. ComboBox1 contains a Supplier Name and TextBox3 contains the invoice Number Then I click on a command button with the following code but it only displays details of the first occurance of the Supplier Name Comments, suggestions etc very greatly appreciated Regards and TIA Jim Burton .................................................. .................. Private Sub CommandButton2_Click() Dim InvMo As Integer SuppName = ComboBox1 InvNo = Format(TextBox2, "####") With Worksheets("Invoices") Set C = .Range("a:a").Find(SuppName, LookIn:=xlValues) If Not C Is Nothing And .Cells(C.Row, 3) < InvNo Then firstAddress = C.Address Do Set C = .Range("a:a").FindNext(C) Loop While Not C Is Nothing And .Cells(C.Row, 3) < InvNo And C.Address < firstAddress TextBox1 = .Cells(C.Row, 2) TextBox3 = .Cells(C.Row, 4) End With End Sub .................................................. ......... I have tried numerous pieces of cod and they all stop at the 1st occurence |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using 'Find' and 'FindNext' in vba
Ooops - Just spotted it ... it was a typo
"Dim InvMo As Integer" ......... should have been .......... "Dim InvNo As Integer" But any suggestions comments will still be appreciated Regards again and Thanks P.S. - Apologies for top-posting but I thought under the circumstances it would be acceptable "SA3214 @Eclipse.co.uk" <sa3214<No Spam wrote in message ... I have been struggling to write a short piece of code to do the following Sheets("Invoices").Range("a:a") contains Supplier Names Sheets("Invoices").Range("c:c") contains Invoice Numbers I have created a userform to to display the invoice details after specifying the above details i.e. ComboBox1 contains a Supplier Name and TextBox3 contains the invoice Number Then I click on a command button with the following code but it only displays details of the first occurance of the Supplier Name Comments, suggestions etc very greatly appreciated Regards and TIA Jim Burton .................................................. ................. Private Sub CommandButton2_Click() Dim InvMo As Integer SuppName = ComboBox1 InvNo = Format(TextBox2, "####") With Worksheets("Invoices") Set C = .Range("a:a").Find(SuppName, LookIn:=xlValues) If Not C Is Nothing And .Cells(C.Row, 3) < InvNo Then firstAddress = C.Address Do Set C = .Range("a:a").FindNext(C) Loop While Not C Is Nothing And .Cells(C.Row, 3) < InvNo And C.Address < firstAddress TextBox1 = .Cells(C.Row, 2) TextBox3 = .Cells(C.Row, 4) End With End Sub .................................................. ........ I have tried numerous pieces of cod and they all stop at the 1st occurence |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using 'Find' and 'FindNext' in vba
"Option Explicit" at the top of your code would have found that for you. With
it all variables must be explicitly declared. Without it VB will create new variables for you on the fly (which is what happened to you). You can set up your VB to automatically include option explicit at the top of all of your new code modules and sheets by selecting tools - Options - General - Require Variable Declarations. HTH "SA3214 @Eclipse.co.uk" wrote: Ooops - Just spotted it ... it was a typo "Dim InvMo As Integer" ......... should have been .......... "Dim InvNo As Integer" But any suggestions comments will still be appreciated Regards again and Thanks P.S. - Apologies for top-posting but I thought under the circumstances it would be acceptable "SA3214 @Eclipse.co.uk" <sa3214<No Spam wrote in message ... I have been struggling to write a short piece of code to do the following Sheets("Invoices").Range("a:a") contains Supplier Names Sheets("Invoices").Range("c:c") contains Invoice Numbers I have created a userform to to display the invoice details after specifying the above details i.e. ComboBox1 contains a Supplier Name and TextBox3 contains the invoice Number Then I click on a command button with the following code but it only displays details of the first occurance of the Supplier Name Comments, suggestions etc very greatly appreciated Regards and TIA Jim Burton .................................................. ................. Private Sub CommandButton2_Click() Dim InvMo As Integer SuppName = ComboBox1 InvNo = Format(TextBox2, "####") With Worksheets("Invoices") Set C = .Range("a:a").Find(SuppName, LookIn:=xlValues) If Not C Is Nothing And .Cells(C.Row, 3) < InvNo Then firstAddress = C.Address Do Set C = .Range("a:a").FindNext(C) Loop While Not C Is Nothing And .Cells(C.Row, 3) < InvNo And C.Address < firstAddress TextBox1 = .Cells(C.Row, 2) TextBox3 = .Cells(C.Row, 4) End With End Sub .................................................. ........ I have tried numerous pieces of cod and they all stop at the 1st occurence |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using 'Find' and 'FindNext' in vba
"Jim Thomlinson" wrote in message ... "Option Explicit" at the top of your code would have found that for you. With it all variables must be explicitly declared. Without it VB will create new variables for you on the fly (which is what happened to you). You can set up your VB to automatically include option explicit at the top of all of your new code modules and sheets by selecting tools - Options - General - Require Variable Declarations. HTH SNIP I thought that I had already done that ... but thanks anyway for your input |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Findnext | Excel Discussion (Misc queries) | |||
Find Findnext in selected range | Excel Programming | |||
Find, Findnext VBA Loop | Excel Programming | |||
FindNext | Excel Programming | |||
Find...FindNext Problem | Excel Programming |