vlookup - function not defined

jryan15

Board Regular
Joined
Jan 27, 2005
Messages
158
i must be the newbies of newbies since i can not even get the vlookup funciton to work at all.

i am just trying to see if an entered cell value (within range A7:A42) matches a value within another range E7:E62. if not, i want to pop up an error and clear the cell.

every way i try to use vlookup, i get a "function not defined" error...

any help would be great! thanks.
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

cherria

Well-known Member
Joined
Nov 17, 2004
Messages
708
Hi, Welcome to the board.

It sounds like what you really want is data validation.

Take a look at Date->Validation

select list and define your source as E7:E62

Then click on the error alert tab and define the error message you want to pop up.
 

jryan15

Board Regular
Joined
Jan 27, 2005
Messages
158
thanks for the quick response, and that looks to do exactly what i asked.

actually i was planning on using the resultant of the vlookup in some code that i'm working on. i might also have some more uses for the vlookup function too.
 

Seti

Well-known Member
Joined
May 19, 2002
Messages
2,916
Replace VLOOKUP with Application.Worksheetfunction.VLOOKUP. VLOOKUP Is not a supported function in VBA, you need to use Excel's functionality here.
 

jryan15

Board Regular
Joined
Jan 27, 2005
Messages
158

ADVERTISEMENT

ah... things are clearing up somewhat now...

the vlookup is atleast working now, but i'm getting errors. the code that i'm using is below

Code:
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 1 And Target.Row >= 7 And Target.Row <= 42 Then
    test = Application.WorksheetFunction.VLookup(Target.Address, Application.ActiveSheet.Cells("L7:L62"), False)
    MsgBox (test)
End If

End Sub

the goal is just to immediately check the data input into A7:A42 against the values of cells L7:L62. if the A7:A42 data isn't found in L7:L62, an error message is to display and the data cleared. thanks.
 

cherria

Well-known Member
Joined
Nov 17, 2004
Messages
708
try changing this
test = Application.WorksheetFunction.VLookup(Target.Address, Application.ActiveSheet.Cells("L7:L62"), False)

to this

test = Application.WorksheetFunction.VLookup(Target.Address, Application.ActiveSheet.Range("L7:L62"), 1, False)
 

jryan15

Board Regular
Joined
Jan 27, 2005
Messages
158

ADVERTISEMENT

didn't work... i'm getting a debug error on the line for something. i just don't undrestand the objects and properties well enough codewise to get this straight...

it's aggrevating knowing exactly what you want to do, and not being able to write the code to do it.
 

Seti

Well-known Member
Joined
May 19, 2002
Messages
2,916
You do need to make the change that cherria suggested as your version of the formula was missing the column to lookup in. When it blows up, is it because the value isn't in the L7:L62 range?
 

jryan15

Board Regular
Joined
Jan 27, 2005
Messages
158
thanks for the tips. i'm working on a slightly different approach that may not need the vlookup option. thanks.
 

Forum statistics

Threads
1,147,673
Messages
5,742,530
Members
423,736
Latest member
dracula cyrus

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top