squippe
01-31-2017, 05:12 AM
This code works great except it ignores the inserted comment in the cell. How to amend the code in order to copy the text & the comment?
Sub vertailu()
Dim lastRow1 As Long
Dim lastRow2 As Long
Dim tempVal As String
lastRow1 = Sheets("11").Range("K" & Rows.Count).End(xlUp).Row
lastRow2 = Sheets("12").Range("K" & Rows.Count).End(xlUp).Row
For sRow = 2 To lastRow1
tempVal = Sheets("11").Cells(sRow, "K").Text
For tRow = 2 To lastRow2
If Sheets("12").Cells(tRow, "K") = tempVal Then
Sheets("12").Cells(tRow, "O") = Sheets("11").Cells(sRow, "O")
End If
Next tRow
Next sRow
Dim match As Boolean
'now if no match was found, then put NO MATCH in cell
For lRow = 2 To lastRow2
match = False
tempVal = Sheets("12").Cells(lRow, "K").Text
For sRow = 2 To lastRow1
If Sheets("11").Cells(sRow, "K") = tempVal Then
match = True
End If
Next sRow
If match = False Then
Sheets("12").Cells(lRow, "O") = "NO MATCH"
End If
Next lRow
End Sub
Sub vertailu()
Dim lastRow1 As Long
Dim lastRow2 As Long
Dim tempVal As String
lastRow1 = Sheets("11").Range("K" & Rows.Count).End(xlUp).Row
lastRow2 = Sheets("12").Range("K" & Rows.Count).End(xlUp).Row
For sRow = 2 To lastRow1
tempVal = Sheets("11").Cells(sRow, "K").Text
For tRow = 2 To lastRow2
If Sheets("12").Cells(tRow, "K") = tempVal Then
Sheets("12").Cells(tRow, "O") = Sheets("11").Cells(sRow, "O")
End If
Next tRow
Next sRow
Dim match As Boolean
'now if no match was found, then put NO MATCH in cell
For lRow = 2 To lastRow2
match = False
tempVal = Sheets("12").Cells(lRow, "K").Text
For sRow = 2 To lastRow1
If Sheets("11").Cells(sRow, "K") = tempVal Then
match = True
End If
Next sRow
If match = False Then
Sheets("12").Cells(lRow, "O") = "NO MATCH"
End If
Next lRow
End Sub