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

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

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

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