vlookup - function not defined

[jryan15]

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.

 

[cherria]

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]

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]

Replace VLOOKUP with Application.Worksheetfunction.VLOOKUP. VLOOKUP Is not a supported function in VBA, you need to use Excel's functionality here.

 

[jryan15]

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

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]

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]

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]

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]

thanks for the tips. i'm working on a slightly different approach that may not need the vlookup option. thanks.