Logic for vba vlookup from one sheet and apply to another sheet based on range

[trpltwo]

Hello, thank you in advance for your help.

Please refer to attached sample spreadsheet. I would like to add a macro button, to lookup sheet1 tab based on column A "EventID" and lookup the same "EventID" in sheet2 tab and apply column B "Factors" into sheet1 of column C.

Also sheet2 will continue to be updated to add more or amend, so the idea is for the macro to be able to also update/amend based on what's in sheet2.

Noting there is a simple formula without using macro however, reason we need this in VBA is because data is overridden in sheet1 daily and where there's empty cells in column C, some users won't be able to know or confuse other users who are not comfortable manually entering formulas.

thank you again in advance.

 

[kevin9999]

One option:

Option Explicit
Sub Update()
    Dim rng As Range
    Set rng = Sheets("Sheet2").Range("A2", Sheets("Sheet2").Cells(Rows.Count, "B").End(xlUp))
    
    With Sheets("Sheet1").Range("C2:C" & Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row)
        .Value = Application.IfError(WorksheetFunction.VLookup(.Offset(, -2), rng, 2, False), "ID not found")
    End With
End Sub

 

[trpltwo]

One option:

Option Explicit
Sub Update()
    Dim rng As Range
    Set rng = Sheets("Sheet2").Range("A2", Sheets("Sheet2").Cells(Rows.Count, "B").End(xlUp))
   
    With Sheets("Sheet1").Range("C2:C" & Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row)
        .Value = Application.IfError(WorksheetFunction.VLookup(.Offset(, -2), rng, 2, False), "ID not found")
    End With
End Sub

Thanks kevin999 this is great but the code seem to be overriding the ones already there that are not in sheet2, so its replacing the existing one with "ID not found" which should still remain there and only look at replacing the ones that are located in sheet2.

 

[kevin9999]

Understood. Try the following instead:

Option Explicit
Sub Update_V2()
Application.ScreenUpdating = False
    Dim c As Range
    Sheets("Sheet2").Range("A2", Sheets("Sheet2").Cells(Rows.Count, "B").End(xlUp)).Name = "rng"
    For Each c In Sheets("Sheet1").Range("C2:C" & Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row)
        If c = "" Then
            c.FormulaR1C1 = "=iferror(vlookup(RC1,rng,2,false),""ID Not Found"")"
            c = c
        End If
    Next c
    Application.ScreenUpdating = True
End Sub

 

[trpltwo]

Perfect! Exactly what I needed! Thank you very much kevin9999!

 

[kevin9999]

Perfect! Exactly what I needed! Thank you very much kevin9999!

Glad I could help, and thanks for the feedback