I'm a newbie trying to set-up an Invoice form on Excel -- my set up is:
A combo box for selection of Customers
cboCustomer RowSource=AddRange (in worksheet Add)
ControlSource=Customer (in worksheet Job)
Now I need to find whatever value/string returned to Worksheets("Job").Range("Customer")
in Worksheet("Add").Range("AddRange"). So I can copy the 3 cells beside that value/string.
(Initially I thought this could be accomplished in combo box alone but turns out it doesn't support multicolumn...)
I am also having problem with the Find Method because most examples I seen it only works with pre-determined value
I've spent the past few hours trying to figure this out...
Any help is greatly appreciated!
Thanks,
Ame
Find Method - enter value on one worksheet and search in another
Hot Topic
- .onAction assign value
- ScreenUpdating
- CRM 3.0 - Insert template & Subject is truncated
- Strange problem with saving to a FTP-server
- Excel Macro to Capture Live Webcam Image?
- Error setting range to hidden in Excel 2003, not in 2000
- String Functions
- For Next stops at 16
- Trouble while dealing with Share Point Object Model
- RunTime Error 3704 Loading Recordset from Stored Procedure
Find Method - enter value on one worksheet and search in another
Rob Wilson
Here is a variation on the find method which does not require the selecting of sheets or cells.
The result of the Find is assigned to a range variable. This is then tested for a successful match.
The address and contact information is the passed to the named ranges using the Offset method.
Private Sub cboCustomer_AfterUpdate()
Dim sCustomer As String
Dim rngFind As Range
sCustomer = Worksheets("Job").Range("Customer").Value
Set rngFind = Worksheets("AddData").Cells.Find(What:=sCustomer, _
After:=ActiveCell, LookIn:=xlValues, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)
If rngFind Is Nothing Then
MsgBox "No match found", vbExclamation
Else
With Worksheets("Job")
.Range("Add1") = rngFind.Offset(0, 1).Value
.Range("Add2") = rngFind.Offset(0, 2).Value
.Range("Add3") = rngFind.Offset(0, 3).Value
.Range("Contact") = rngFind.Offset(0, 4).Value
End With
End If
End Sub
A combobox can display multi columns but only 1 of the columns can be bound to a cell.
The rowsource property will need to include the complete table of address information.
ColumnCount property needs to be 5
Private Sub ComboBox1_AfterUpdate()
'
' Multi column combo box
'
Dim lngIndex As Long
lngIndex = ComboBox1.ListIndex
With Worksheets("Job")
.Range("Add1") = ComboBox1.List(lngIndex, 1)
.Range("Add2") = ComboBox1.List(lngIndex, 2)
.Range("Add3") = ComboBox1.List(lngIndex, 3)
.Range("Contact") = ComboBox1.List(lngIndex, 4)
End With
End Sub
KenHerman
But please give me some comments for I am sure there's room for improvements:-
Private Sub cboCustomer_AfterUpdate()
Dim sCustomer As String
Dim sAdd1 As String
Dim sAdd2 As String
Dim sAdd3 As String
Dim sContact As String
sCustomer = Worksheets("Job").Range("Customer")
Worksheets("AddData").Select
Cells.Find(What:=sCustomer, After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate
ActiveCell.Offset(0, 1).Select
sAdd1 = ActiveCell.FormulaR1C1
ActiveCell.Offset(0, 1).Select
sAdd2 = ActiveCell.FormulaR1C1
ActiveCell.Offset(0, 1).Select
sAdd3 = ActiveCell.FormulaR1C1
ActiveCell.Offset(0, 1).Select
sContact = ActiveCell.FormulaR1C1
Worksheets("Job").Select
Range("Add1") = sAdd1
Range("Add2") = sAdd2
Range("Add3") = sAdd3
Range("Contact") = sContact
End Sub
johnvarney
That makes the macro so much tidier.
Really appreciate your help!