Cross referencing if the cell is blank.

Question

Hello All, this is my first post here. I hope to explain my question properly.
I need to run a Macro on a column.
The cell should display a name. This name is linked to an order number.
When I pull the report, sometimes the name is missing but I have another sheet matching all names with order numbers.
So my idea is the following, If the cell is blank, I want to get the name from another sheet using the order number as a reference.
IF the cell is not blank, then nothing is required as the name is already there.
The name should be displayed in column B, and the order number is in column C.
any help is much appreciated.
Thanks in advance.

hansvogelaar · Answer

minasamy&nbsp;
Let's say that the other sheet is named List, with order numbers in column A and the corresponding names in column B.
On the sheet that the macro should act on, names are in column D and order numbers in column F.
You can change all this in the macro.
Sub GetMissingNames()
    ' Change the constants as needed.
    Const ListSheet = "List"
    Const ListNumCol = "A"
    Const ListNameCol = "B"
    Const DataNumCol = "F"
    Const DataNameCol = "D"

Dim wshData As Worksheet
    Dim wshList As Worksheet
    Dim r As Long
    Dim m As Long
    Dim rngFound As Range

Application.ScreenUpdating = False
    Set wshData = ActiveSheet
    Set wshList = Worksheets(ListSheet)
    m = wshData.Cells(wshData.Rows.Count, DataNumCol).End(xlUp).Row
    For r = 2 To m
        If wshData.Cells(r, DataNameCol).Value = "" Then
            Set rngFound = wshList.Columns(ListNumCol).Find(What:=wshData.Cells(r, DataNumCol).Value, _
                    LookIn:=xlValues, LookAt:=xlWhole)
            If Not rngFound Is Nothing Then
                wshData.Cells(r, DataNameCol).Value = wshList.Cells(rngFound.Row, ListNameCol).Value
            End If
        End If
    Next r
    Application.ScreenUpdating = True
End Sub

Forum Discussion

Cross referencing if the cell is blank.

1 Reply

Resources