vlookup worksheet formula error: sub or function not defined?

[crazyfray]

Hi all,

I've searched the forum for a response on this, but I don't think it's because of vlookup itself - I must be making some kind of basic error I'm not seeing .

Background: I have a form with a textbox, and want to use vlookup each time the value of the textbox changes in order to check whether the value is one that exists on a background worksheet.

Problem: Even though vlookup is shown as a worksheet function available in VBA, I get 'compile error: sub or function not defined'.

Sub txt_AcYear_Change()
Dim AcademicYear As String
    AcademicYear = VLookup(txt_AcYear.Text, Worksheets("_background").Range("C:C"), 1, False)
    lbl_GetIsValid.Caption = AcYear 'This will show whether the textbox value ' has been found
 
End Sub

...just to confirm, this is the code attached to the textbox change event.

Can anyone see what I'm doing wrong?

Thanks for reading,

Fray

UPDATE: Also, changing it to

 AcademicYear = Application.VLookup(txt_AcYear.Text, Worksheets("_background").Range("C:C"), 1, False)

gives me a type mismatch error instead, in case anyone was going to suggest that.

[Charlize]

Try using this :[VBA]Application.WorksheetFunction.VLookup[/VBA]

[Bob Phillips]

Or even just Application.VLookup

[Norie]

If you are getting a type mismatch error then it's probably because the Vlookup isn't finding a match and is returning N/A.

[crazyfray]

Try using this :[vba]Application.WorksheetFunction.VLookup[/vba]

Or even just Application.VLookup

If you are getting a type mismatch error then it's probably because the Vlookup isn't finding a match and is returning N/A.

From your responses, seems like I had 2 problems - not calling the vlookup properly, and when I did, the type mismatch (thanks Norie) kicks in.

I don't want the code to stop at this point if no value is matched, so I guess I'll have to see what I can do. I'm sure there must be a way around it, else vlookup would be pretty useless in any situation where you can't guarantee a valid return.

Thanks - will post back here when I'm at my comp to try these out.

[Norie]

On Error Resume Next could be one solution.

Another could be to use a formula like CountIf to see if the value actually exists before using the VLookup.

CountIf will return 0 not an error if the value isn't found.

Another solution might be to use a different method.

I'm thinking a combobox or listbox instead of a textbox.

Note the root cause of the problem could possibly be the use of the change event - that get's triggered every time the textbox changes.

So you might be ending up trying to look up partial values.

Eg let's say you are looking for Value1

As you type in the textbox the code will be triggered and the first thing to be looked for would be V.

[RichardSchollar]

On Error Resume Next prior to performing the Vlookup (you could change this for a Match function or, better, to a VBA Find in which case you won't return an error if the value isn't found), and then test for an error value returned eg:

On Error Resume Next
AcademicYear = Application.VLookup(txt_AcYear.Text, Worksheets("_background").Range("C:C"), 1, False)
If Err=0 Then 'value found
'blah blah blah

[rory]

Or declare your variable as a variant, use Application.Vlookup and then check for [VBA]If IsError(varAcademicYear) Then[/VBA]...
Just as an alternative. Since you don't actually need VLookup, I would go with Application.Countif though.
Regards,
Rory

[crazyfray]

Apologies for huge delay in responding here - several deadlines in a row make for me forgetting about this thread (very unfair of me)! Thanks to all those who have replied.

Looking back at this, I changed this to instead work from offsets instead of vlookup. So, it searches for the input of AcademicYear, knows that the table of data starts 2 cells below that and offsets for the varying values (would post it, but not really relevant to the problem I was having).

I'd mark this as solved, but I remade it from the ground up instead, and can't really help anyone that encounters the same problem (though I've used Excel worksheet functions in VBA and not had a problem, which makes me certain it was user error :P ).

/me going to go and post a couple of replies to absolve myself for not replying...