Compare values in 2 worksheets

Hi everyone!
We need to match records between 2 worksheets using column "K" in both sheets. It contains a unique item number for each record (like a database)

Using a loop, if the active cell in column D of worksheet1 is blank, then the value in column D in worksheet2 is printed to the cell in worksheet1 where the cell in column K are the same in both worksheets.

Dim i As Long

For i = 7 To lastRow
If Cells(i, "D").Value = "" And Cells(i, "K").Value = 'Value in column "K" of worksheet2 Then
Cells(i, "D").Value = 'Value from the cell in column "D" of worksheet2
End If
Next i

Thanks in advance everyone!

I have assumed the data on Sheet2 also starts in row 7. The loop could
work like this:

Sub withloop()

Dim lastRow As Long
Dim eRow As Long
Dim lRange As Range
Dim fRange As Range
Dim cRange As Range
Dim cell As Range

lastRow = Sheets("Sheet1").Cells(Rows.Count, 11).End(xlUp).Row
eRow = Sheets("Sheet2").Cells(Rows.Count, 11).End(xlUp).Row
With Sheets("Sheet1")
Set cRange = .Range(.Cells(7, 4), .Cells(lastRow, 4))
End With
With Sheets("Sheet2")
Set lRange = .Range(.Cells(7, 11), .Cells(eRow, 11))
End With

For Each cell In cRange
If cell.Value = "" Then
Set fRange = lRange.Find(cell.Offset(0, 7).Value)
If Not fRange Is Nothing Then
cell.Value = fRange.Offset(0, -7).Value
Set fRange = Nothing
End If
End If
Next cell

End Sub

Or you could do it without a loop (mostly for my own entertainment):

Sub NoLoop()
Dim lastRow As Long
Dim eRow As Long
lastRow = Sheets("Sheet1").Cells(Rows.Count, 11).End(xlUp).Row
eRow = Sheets("Sheet2").Cells(Rows.Count, 11).End(xlUp).Row
With Sheets("Sheet1").Range(Cells(7, 4), Cells(lastRow, 4)). _
SpecialCells(xlCellTypeBlanks)
.FormulaR1C1 = _
"=IF(ISNA(INDEX(Sheet2!R7C4:R" & eRow & "C4,MATCH(RC[7]," _
& "Sheet2!R7C11:R" & eRow & "C11,0),1)),"""",INDEX" _
& "(Sheet2!R7C4:R" & eRow & "C4,MATCH(RC[7],Sheet2!" _
& "R7C11:R" & eRow & "C11,0),1))"
End With
End Sub

Note this method will not give the desired results if you have more than
8100 odd non contiguous ranges with blank cells in column D on sheet1.

Or finally if you didn't want to keep the data you already have in
column D, enter this formula in D7 and copy down:

=IF(ISNA(INDEX(Sheet2!$D$7:$D$100,MATCH(K7,Sheet2!$K$7:$K$100,0),1)),"",INDEX(Sheet2!$D$7:$D$100,MATCH(K7,Sheet2!$K$7:$K$100,0),1))

Hope this helps
Rowan

mthomas wrote:
> Hi everyone!
> We need to match records between 2 worksheets using column "K" in both
> sheets. It contains a unique item number for each record (like a
> database)
>
> Using a loop, if the active cell in column D of worksheet1 is blank,
> then the value in column D in worksheet2 is printed to the cell in
> worksheet1 where the cell in column K are the same in both worksheets.
>
> Dim i As Long
>
> For i = 7 To lastRow
> If Cells(i, "D").Value = "" And Cells(i, "K").Value = 'Value in
> column "K" of worksheet2 Then
> Cells(i, "D").Value = 'Value from the cell in column "D" of
> worksheet2
> End If
> Next i
>
> Thanks in advance everyone!
>
>

Hey Rowan,

Just a quick note to say thanks! I chose to use the loop and it works great!