VBA Offset and Match

[RobCam01]

Hi All;

Hope you can help.

I have the following statement

If IsText(Me!cmbCustomer) Then
Me!txtEmail.Value = Application.WorksheetFunction.Offset(ActiveWorkbook.Worksheets("Website Orders").Range("O12"), Application.WorksheetFunction.Match(cmbCustomer.Value, ActiveWorkbook.Worksheets("Website Orders").Range("O13:O16"), 1), 10)
End If

This does not seem to work, any suggestions?

Kind Regards

 

[Yongle]

What is Me (worksheet? userform?)
What is cmbCustomer (a combo box? a named range)
What is txtEmail (a text box? a named range)

 

[RobCam01]

What is Me (worksheet? userform?)
What is cmbCustomer (a combo box? a named range)
What is txtEmail (a text box? a named range)

Hi.. should have been more detailed, apologies.

The intention is to have the email of a certain customer in a combo box appear in a text box.

Me is the is the user form, cmbCustomer is a combo box and txtEmail a text box.

The customers are listed in the sheet "Website Orders" O13:O16 (to be expanded later) and the emails 10 column therefrom.

Problem is that I get a Run-Time error 1004 " Unable to get the Match properly of the WorksheetFunction class"

 

[Fluff]

AFAIK, you cannot use the xl function offset in VBA as it has it's own.
Try

If Len(Me.cmbCustomer) > 0 Then
   a = Application.Match(Me.cmbCustomer.Value, Sheets("Website Orders").Range("O13:O16"), 1)
   Me.txtEmail.Value = Range("O12").Offset(a, 10).Value
End If

Also I'm not sure what IsText is, but as a combobox will always return a text value, it seems redundant.

 

[JackDanIce]

Why don't you use

Range("O13:O16").Find(What:=cmbCustomer.Value, lookin:=xlformulas).Row

Seems excessive and unecessary use of worksheet functions. VBA has its own built in .Offset, you can use .Find to search for an item in a range and if found, return it's row number and column index number

 

[Legacy 421546]

This is not Excel, it's Access, right?
Everything in Access does not work in Excel and vice versa.

 

[RobCam01]

This works perfect! Thanks.

It does however return a error if trying to type in name in the combo box that has not previously been uploaded into the range. (Which I prefer in this instance)

Thanks alot!