Vba Vlookup from userform textbox entry - run-time error 424

[alecwarner]

Trying to pull in an email address from a textbox on a userform.


Worksheet is called DATA
names are listed in column O2:O30
email addresses are listed in P2:P30


ENG = UserForm1.ComboBox2.Value ' Eng
Dim EMAIL As String
EMAIL = Application.WorksheetFunction.VLookup(ENG, Data.Range("O2:O30").Value, 1, False)
MsgBox EMAIL

I keep getting:
Run-time error '424'
Object required

any help would be appreciated?

Thanks

Alge

 

[Joe4]

Your range reference is not correct. It should be:

[COLOR=#333333]EMAIL = Application.WorksheetFunction.VLookup(ENG, [/COLOR][COLOR=#ff0000]Sheets("Data")[/COLOR][COLOR=#333333].Range("O2:O30").Value, 1, False)[/COLOR]

 

[alecwarner]

Thanks

When the code is changed too:

EMAIL = Application.WorksheetFunction.VLookup(ENG, Sheets("DATA").Range("O2:O30").Value, 1, False)

I get a name from column O2:O30.

When the code is changed too:

EMAIL = Application.WorksheetFunction.VLookup(ENG, Sheets("DATA").Range("O2:O30").Value,​

2​

, False)​

I get run-time error 1004

?????????????

Please advise

Alge

 

[jproffer]

Range O2:O30 only has one column. 2 is not a valid column to return from a single column table.