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
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 |