Check data from one sheet in column D to another sheet in column E within the same workbook and if the same a comment is to be added in column L

[StevieMP]

Hi All,

I'm getting myself in a pickle here and it's driving me loopy!!!

Basically, I have the attached spreadsheet and what I am aiming to achieve is when I have clicked the macro button on 'Sheet2' - 'OTC Create DSMatch Email' it creates an email and attaches the appropriate files listed in column 'AC'. This works fine.

As it completes, I want some extra code to check data in the sheet 'MWIRE_DSMatch' in column/row 'D54 to D58' to what has been input in the sheet 'Log' in column 'E' and if there is the same (in this case TEST) put a comment in the 'Log' sheet in column/row 'L14' on the same line, "SP - Email Sent" & Now

In the sheet 'MWIRE_DSMatch' I have only listed one input in column/row 'D54 to D58' however there could be more within that range and if there are I would like multiple inputs to be entered in the sheet 'Log' if they exist in column 'E' of the 'Log' sheet.

I have been playing around with various bits of code but to no avail!

Can someone please assist as I'm pulling my hair out?

Example code :

Dim lr As Long
Dim r As Long

Worksheets("Log").Select

'Find last row in column E with data
lr = Cells(Rows.count, "E").End(xlUp).Row

'Loop through all rows
For r = 2 To lr
If (Cells(r, "E") <> "") And (Cells(r, "L") = "") Then Cells(r, "L") = "SP - Email Sent " & Now
Next r

Many thanks
Steve

Test.xlsm
	E
23	SUMAC
Log

Test.xlsm
	D
55
MWIRE_DSMatch

 

[StevieMP]

Hi All,

I have managed to work out some of the code from a post previously on here and the code I have got to work is this:

Dim w1 As Worksheet, w2 As Worksheet
Dim c As Range, FR As Long

Application.ScreenUpdating = False

Set w1 = Worksheets("MWIRE_DSMatch")
Set w2 = Worksheets("Log")

For Each c In w1.Range("D54:D58", w1.Range("D" & Rows.count).End(xlUp))
FR = 0
On Error Resume Next
FR = Application.Match(c, w2.Columns(5), 0)
On Error GoTo 0
If FR <> 0 Then w2.Range("L" & FR).Value = c.Offset(, 0)
Next c

Application.ScreenUpdating = True

The result is that it's inputting the matched value into the cell in the 'Log' sheet in the corresponding row/column.
In other words if there is the word 'TEST' in the worksheet("MWIRE_DSMatch") in a range and the word 'TEST in the worksheet("Log") input the value "TEST" in row/column "L".

What I would like is to be able to add a comment instead of adding the actual value "TEST".

Can someone assist please?