Fill Down only if Cell Empty or Cell Value needs to be updated

I have a pretty complicated (in my opinion) question.

I have 2 sheets. One is named AssetName Sheet and the other is named AMP Sheet.

I have logic in place that basically says if col. B from the AssetName Sheet matches with a column named Name in the AMP Sheet, pull the AMP ID from the AMP Sheet and paste it in column E of the AssetName Sheet (I know, it's already getting complicated...).

For example, the AssetName Sheet has a name in col. B as USTomato3wheels_Ing_TO_Mobile. The logic found the same name in the AMP Sheet under the Name column and will now pull the associated AMP ID from the AMP Sheet, into column E of the AssetName Sheet.

Here is my working logic:

With Sheets("AssetName Sheet")
    Const F As String = "=IFERROR(INDEX(OFFSET('AMP Sheet'!$A:$A,,MATCH(""ID"",'AMP Sheet'!$1:$1,0)-1)," & _
    "MATCH(B1,OFFSET('AMP Sheet'!$A:$A,,MATCH(""Name"",'AMP Sheet'!$1:$1,0)-1),0)),"""")"
    .Range("E1:E" & .Cells(Rows.Count, "E").End(xlUp).Row).Formula = F
    If Range("B2") <> "" Then
        .Range("E1").AutoFill .Range("E1:E" & .Cells(.Rows.Count, "B").End(xlUp).Row)
    End If
End With

Here is what I'm struggling to figure out.

I'm currently filling down my formula with my logic. I want to modify my logic to do the following:

Only fill down to empty cells, or update existing AMP IDs. For example, if there is already an AMP ID copied over, it shouldn't clear this out, but rather, it should update the AMP ID if there's an updated match in the AMP Sheet.

Thank you for everyone's help!

Update; This is what I'm attempting to do so far. Go through each cell --> If the cell is empty, apply the formula --> move to the next cell:

Dim startingLine2 As Double
startingLine2 = 2

With Sheets("AssetName Sheet")
    Do While .Cells(startingLine2, 5) = "" And .Cells(startingLine2, 2) <> ""
        Const F As String = "=IFERROR(INDEX(OFFSET('AMP Sheet'!$A:$A,,MATCH(""ID"",'AMP Sheet'!$1:$1,0)-1)," & _
        "MATCH(B1,OFFSET('AMP Sheet'!$A:$A,,MATCH(""Name"",'AMP Sheet'!$1:$1,0)-1),0)),"""")"
        '.Range("E1:E" & .Cells(Rows.Count, "E").End(xlUp).Row).Formula = F'
        .Cells(startingLine2, 5).Formula = F
        startingLine2 = startingLine2   1
    Loop
End With

CodePudding user response：

I would skip the formula approach and use Application.Match() in VBA to perform the lookups.

Untested, but something like this should be close:

Sub Tester()

    Dim wb As Workbook, wsAN As Worksheet, wsAMP As Worksheet, c As Range, m, v, vCurr
    Dim idCol, nameCol
    
    'set up workbook and sheets
    Set wb = ThisWorkbook
    Set wsAN = wb.Worksheets("AssetName Sheet")
    Set wsAMP = wb.Worksheets("AMP Sheet")

    'find the column headers on AMP sheet
    idCol = Application.Match("ID", wsAMP.Rows(1), 0)
    nameCol = Application.Match("Name", wsAMP.Rows(1), 0)
    
    'check column headers were found: exit if not
    If IsError(idCol) Or IsError(nameCol) Then 'check headers were found
        MsgBox "Column headers 'ID' and 'Name' are both needed on sheet 'AMP Sheet'", vbExclamation
        Exit Sub
    End If
    
    Application.ScreenUpdating = False
    For Each c In wsAN.Range("B1:B" & wsAN.Cells(Rows.Count, "B").End(xlUp).Row).Cells
        m = Application.Match(c.Value, wsAMP.Columns(idCol), 0) 'find match in ID column
        If Not IsError(m) Then                   ' `m` will be an error value if no match was made...
            v = wsAMP.Cells(m, nameCol).Value    'pull the corresponding name
            With c.EntireRow.Columns("E")
                If .Value <> v Then .Value = v   'update colE if different value on AMP sheet
            End With
        End If
    Next c

End Sub