Forum Discussion

minasamy's avatar
minasamy
Copper Contributor
Jul 24, 2024

Cross referencing if the cell is blank.

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. 

1 Reply

  • minasamy 

    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

Resources