VBA Worksheet Change Event formula

roykana

Board Regular
Joined
Mar 8, 2018
Messages
246
Office Version
  1. 2010
Platform
  1. Windows
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
VBA Code:
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
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,849
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
Place this macro in the "DB" worksheet code module.
VBA Code:
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

Board Regular
Joined
Mar 8, 2018
Messages
246
Office Version
  1. 2010
Platform
  1. Windows
Place this macro in the "DB" worksheet code module.
VBA Code:
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
VBA Code:
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

Well-known Member
Joined
Apr 11, 2012
Messages
10,849
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
Try:
VBA Code:
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
 
Solution

roykana

Board Regular
Joined
Mar 8, 2018
Messages
246
Office Version
  1. 2010
Platform
  1. Windows
Try:
VBA Code:
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

Well-known Member
Joined
Apr 11, 2012
Messages
10,849
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
You are very welcome. :)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,253
Messages
5,769,051
Members
425,512
Latest member
wannabe_excel_wiz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top