S
SA3214
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
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