Selected Answer
You may accomplish what you describe with a simple Worksheet_Change event procedure. Try the one below.
Private Sub Worksheet_Change(ByVal Target As Range)
' 273
Const TriggerRange As String = "D3,D4,D5" ' specify 3 cells
Const LookupClm As String = "B,A,J" ' columns relate to TriggerRange
Dim Ws As Worksheet ' Data source
Dim LookupRng As Range
Dim Rs As Long ' Ws row number
Dim Clm() As String ' split of LookupClm
Dim Triggers As Range
Dim TriggerID As Integer ' identify the trigger
Dim i As Integer ' loop counter: Triggers
Dim Rt As Long ' Target row
Set Triggers = Range(TriggerRange)
If Not Application.Intersect(Target, Triggers) Is Nothing Then
Application.EnableEvents = False ' prevent next action from calling this proc
With Target
For i = 1 To Triggers.Areas.Count
If Triggers.Areas(i).Address = .Address Then
TriggerID = i
Else
Triggers.Areas(i).ClearContents
End If
Next i
Clm = Split("," & LookupClm, ",") ' align index with TriggerID
Set Ws = Worksheets("Data1")
With Ws
Set LookupRng = .Range(.Cells(1, Clm(TriggerID)), _
.Cells(.Rows.Count, Clm(TriggerID)).End(xlUp))
End With
On Error Resume Next
Rs = WorksheetFunction.Match(.Value, LookupRng, 0)
If Err Then
MsgBox "The search for """ & .Value & """ was unsuccessful.", _
vbInformation, "Invalid search criterium"
Else
With Ws
Set LookupRng = .Range(.Cells(Rs, 1), _
.Cells(Rs, .Columns.Count).End(xlToLeft))
End With
Rt = 7 ' first row to write to (-1)
For i = 1 To LookupRng.Cells.Count
Cells(i + Rt, "C").Value = Ws.Cells(1, i).Value
Cells(i + Rt, "D").Value = LookupRng.Cells(i).Value
Next i
End If
End With
Application.EnableEvents = True
End If
End Sub
You were asking for an approach. Therefore my focus was on how to make sure that only one entry by the user is processed. Like your own sample, my code also doesn't deal with the difference between your 3 Data tabs but the method shown can be extended with little effort.
The part that probably needs a little explaining is the relationship between the two constants TriggerRange and LookupClm at the top of the code. The trigger range is D3:D5 but I specified D3,D4,D5. The difference is that you could specify non-contiguous cells and the code can handle that (not tested).
If any of these cells is changed the procedure will fire. The value entered must be looked up in different columns. D3 must be looked up in column B, D4 in column A and since I couldn't figure out where to look for "DVSC" I assigned column J. You can assign different columns to suit your needs.
All my lookups are done on the same sheet. Of course, columns A, B and J could also be on different sheets. In that case you need to expand the definition of Ws to be different depending upon the TriggerID.
Data are retreved from the worksheet specified by the TriggerID. In the attached workbook select D3, press F2 and ENTER for a demonstration. Of course, the target area could be anywhere in the workbook.
