beep on unsucessful vlookup

[flemard]

I am looking for a formula to produce a Beep when vlookup fails to find a match in the reference table.
This will alert the operator to a mis keyed entry
A non VBA solution is preferred.
many thanks
Don.

 

[shg]

Welcome to the board.

There's no way to make Excel beep without VBA.

 

[flemard]

Thanks for the reply.
Is there a VBA solution you could recommend?
I am looking for an audible alert when vlookup fails to find a match in the reference table.
Many thanks
Don

 

[shg]

I would recommend that you use conditional formatting to make the cell bright red if it contains #N/A as a result of the lookup failing. A beep gives the user no clue where the problem is.

 

[flemard]

Excellent idea. I'll add conditional formatting but the audible alert will ensure they look at the screen, rather than plowing ahead with data entry.
Would you have a solution for that?
thanks
Don

 

[shg]

Function VVLookup(vWhat As Variant, _
                  av As Variant, _
                  iCol As Long, _
                  Optional bRangeLookup As Boolean = True) As Variant
    VVLookup = Application.VLookup(vWhat, av, iCol, bRangeLookup)
    If IsError(VVLookup) Then Beep
End Function

I predict users will be chasing you with pitchforks in the night.