Vlookup and error handling

[Dicko]

I currently have a worksheet (Rego) whereby I enter data in a cell which then looks for matching data in another worksheet (Data) and returns results found to cells either as filled or blank depending on a data match result. This has worked well to date.

However I wish to advance the error handling when no data match is made.

If there is no data match I want a message box to appear stating "data not found" and then go to worksheet (Data).

Error handling is still my greatest difficulty!

I would kindly appreciate assistance and suggestions in adding instruction to my current code and achieving that result.

The current Worksheet (Rego) code is as follows...

Private Sub Worksheet_Change(ByVal Target As Range)

ActiveSheet.Protect Password:="xxxx", UserInterfaceOnly:=True
ActiveSheet.AutoFilterMode = False
ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 3).Select
col = Left(Target.Address, 2)
If col = "$D" Then Target.Offset(0, -3) = Now()
col = Left(Target.Address, 2)
If col = "$D" Then Target.Offset(0, -2) = Now()
col = Left(Target.Address, 2)
If col = "$G" Then Target.Offset(0, -4) = Now()
Dim Cell As Range

If Target.Column <> 4 Then Exit Sub
Application.EnableEvents = False
For Each Cell In Target
With Cell.Offset(, 4)
If IsEmpty(Cell) Then
.ClearContents
Else
.Value = Evaluate("=VLOOKUP(""" & Cell.Value & """,'Data'!A:E,2,FALSE)")
If IsError(.Value) Then .Value = ""
End If
End With
Next Cell
Application.EnableEvents = True

(REPEATS THE LOOKUP FOR NEXT 3 CELLS)

ActiveSheet.AutoFilterMode = False
ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 3).Select
ActiveSheet.Protect Password:="xxxx", UserInterfaceOnly:=True

End Sub
<!-- -->

Reply to Private Message Forward <!--
-->

 

[venkat1926]

I wonder whether it is necessary to have a complicated errorhandling for vlookup

in the woirksheet formula you can have

=if(isNA(vlookup(..........)),"",vlookup(..................)))

this give a blan entry if there is no vlookup match

see for details
Getting Rid of the Vlookup #N/A Error Code

perhaps no need for a macro. you can copy the vlookup formula down provided the reference range is made constant range by using dollar sigbs.

you can convert this into a code in macro if necessarey

 

[Dicko]

I wonder whether it is necessary to have a complicated errorhandling for vlookup

in the woirksheet formula you can have

=if(isNA(vlookup(..........)),"",vlookup(..................)))

this give a blan entry if there is no vlookup match

see for details
Getting Rid of the Vlookup #N/A Error Code

perhaps no need for a macro. you can copy the vlookup formula down provided the reference range is made constant range by using dollar sigbs.

you can convert this into a code in macro if necessarey

Thank you for your reply.

My very limited vba knowledge may have resulted in a misunderstanding of what I wanted to achieve.

After playing around with code I came up with the following (a fluke I must add) which does what I seek, however, is there a way of streamlining this instruction?

I inserted this code between lines:
If IsError(.Value) Then .Value = ""
and
End If

If .Value = "" Then ActiveCell.Offset(-1, 0).Select
If .Value = "" Then ActiveCell.Copy
If .Value = "" Then Sheet5.Select
If .Value = "" Then ActiveCell.PasteSpecial Paste:=xlPasteValues
If .Value = "" Then MsgBox "THERE IS NO RECORD. COMPLETE THE DETAILS NOW!", vb, "ERROR DETECTED!"

The above code achieves what I seek but looks "messy" to me.

Would appreciate any suggestions.

 

[venkat1926]

sorry that is NOT VBA. that is a formula in an empty cell. you do not need vba or a macro. use formula it is easier and the cormula can be copied;.