VBA Worksheet Change Event formula

[roykana]

Dear All Master,

I want the vba code in the db sheet in the table name which is "DB".
I want in the id column I just type and immediately enter or type and immediately tab, copy the value
then it will automatically appear in the value in the Name & Job column.

This is my link : VBA Worksheet Change Event formula.xlsm
file

ption Explicit
Sub multivlookupV2()
Application.ScreenUpdating = False
 With Range(Cells(2, 2), Cells(2, 2).End(xlDown))
       .FormulaR1C1 = "=IFERROR(VLOOKUP([@ID],MASTER,2),"""")"
       .Value = .Value
 End With
 With Range(Cells(2, 3), Cells(2, 3).End(xlDown))
       .FormulaR1C1 = "=IFERROR(VLOOKUP([@ID],MASTER,3),"""")"
       .Value = .Value
 End With
 Application.ScreenUpdating = True
 End Sub

thanks
roykana

 

[mumps]

Place this macro in the "DB" worksheet code module.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
    Dim fnd As Range
    Set fnd = Sheets("MASTER").Range("A:A").Find(Target, LookIn:=xlValues, lookat:=xlWhole)
    If Not fnd Is Nothing Then
        Target.Offset(, 1).Resize(, 2).Value = fnd.Offset(, 1).Resize(, 2).Value
    End If
End Sub

 

[roykana]

Place this macro in the "DB" worksheet code module.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
    Dim fnd As Range
    Set fnd = Sheets("MASTER").Range("A:A").Find(Target, LookIn:=xlValues, lookat:=xlWhole)
    If Not fnd Is Nothing Then
        Target.Offset(, 1).Resize(, 2).Value = fnd.Offset(, 1).Resize(, 2).Value
    End If
End Sub

dear sir,
I changed the master table so that it is imperfect.
this is my link :VBA Worksheet Change Event formula-R1.xlsm
file

Option Explicit
Sub multivlookupV2()
Application.ScreenUpdating = False
 With Range(Cells(2, 2), Cells(2, 2).End(xlDown))
       .FormulaR1C1 = "=IFERROR(VLOOKUP([@ID],MASTER,2),"""")"
       .Value = .Value
 End With
 With Range(Cells(2, 3), Cells(2, 3).End(xlDown))
       .FormulaR1C1 = "=IFERROR(VLOOKUP([@ID],MASTER,3),"""")"
       .Value = .Value
 End With
 With Range(Cells(2, 4), Cells(2, 4).End(xlDown))
       .FormulaR1C1 = "=IFERROR(VLOOKUP([@ID],MASTER,6),"""")"
       .Value = .Value
 End With
 Application.ScreenUpdating = True
 End Sub

thanks
roykana

 

[mumps]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
    Dim fnd As Range
    Set fnd = Sheets("MASTER").Range("A:A").Find(Target, LookIn:=xlValues, lookat:=xlWhole)
    If Not fnd Is Nothing Then
        Target.Offset(, 1).Resize(, 3).Value = Array(fnd.Offset(, 1), fnd.Offset(, 3), fnd.Offset(, 5))
    End If
End Sub

 

[roykana]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
    Dim fnd As Range
    Set fnd = Sheets("MASTER").Range("A:A").Find(Target, LookIn:=xlValues, lookat:=xlWhole)
    If Not fnd Is Nothing Then
        Target.Offset(, 1).Resize(, 3).Value = Array(fnd.Offset(, 1), fnd.Offset(, 3), fnd.Offset(, 5))
    End If
End Sub

Dear Mr. mumps,

Thank you very much. the code you provide works perfectly.

Thanks
roykana

 

[mumps]

You are very welcome.