Forum Discussion
minasamy
Jul 24, 2024Copper Contributor
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
Sort By
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