don't use something like vlookup, use vloopup

Dim res as Variant
res =
Application.Vlookup(Range("A1"),Worksheets("Sheet2 ").Range("A1:E200),4,False
)
if not iserror(res) then
msgbox "Returned value is " & res
End if

or to make it easier to do what you want, use Match instead

Dim res as Variant
set rng = Worksheets("Sheet2").Range("A1:E200")
res = Application.Match(Range("A1"), rng,0)
if not iserror(res) then
set rng1 = rng(res).offset(0,4)
msgbox "value returned is: " & res & " at " & rng1.Address(0,0,xlA1,True)
End if

now you can use rng1 to update the cell.


--
Regards,
Tom Ogilvy


"Cheryl" wrote in message
...
I have a user form that contains a combobox that displays all of our
vendors. When I select a vendor, I want to use VBA to retrieve the data for
that vendor such as address.. How do I do this using something similar to
vlookup on the worksheet. Basically, once I display the data.. I want to be
able to edit it.. such as fixing the address and write back to the excel
table it used to retrieve the information..

Thanks in advance for your suggestions or link to a useful resource where
I can find out how to use it...

You could try using the FIND object in the combobox_click event

set rg = Worksheets(1).Range("a1:a500")
With rg 'look in column a for the va
Set c = .Find(combo1.text, lookin:=xlValues)
If Not c Is Nothing Then


End Wit

--
Message posted from http://www.ExcelForum.com

Thank you all... I got this to work..

Private Sub cboBoxCustomer_Change()

Dim SourceRange As Range

Dim Val1 As String

Set SourceRange = Range(cboBoxCustomer.RowSource)

Val1 = cboBoxCustomer.Value

txtVendorID.Text = SourceRange.Offset(cboBoxCustomer.ListIndex, -2).Resize(1, 1).Value
txtType.Text = SourceRange.Offset(cboBoxCustomer.ListIndex, -1).Resize(1, 1).Value
txtName.Text = SourceRange.Offset(cboBoxCustomer.ListIndex, 0).Resize(1, 1).Value
txtDivision.Text = SourceRange.Offset(cboBoxCustomer.ListIndex, 1).Resize(1, 1).Value
txtAddress1.Text = SourceRange.Offset(cboBoxCustomer.ListIndex, 2).Resize(1, 1).Value

etc...

so it now fills in my form with the proper data based on the combo box selection... However... I need to use that information now.. to be able to update the record, once found.... Basically.. I am using the combobox to find the record.. that is tied to a worksheet... Now, I need to be able to update or delete that record based on a command button. I can't seem to make it update.... I think it is because I can't seem to get it to see the row that the found record is currently on to change it.

"dmang " wrote:

You could try using the FIND object in the combobox_click event

set rg = Worksheets(1).Range("a1:a500")
With rg 'look in column a for the va
Set c = .Find(combo1.text, lookin:=xlValues)
If Not c Is Nothing Then


End With


---
Message posted from http://www.ExcelForum.com/