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!


--
mthomas
------------------------------------------------------------------------
mthomas's Profile: http://www.excelforum.com/member.php...o&userid=25649
View this thread: http://www.excelforum.com/showthread...hreadid=467564

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,MAT CH(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 work
great

--
mthoma
-----------------------------------------------------------------------
mthomas's Profile: http://www.excelforum.com/member.php...fo&userid=2564
View this thread: http://www.excelforum.com/showthread.php?threadid=46756