Do While..Loop doesn't seem to be looping through

[StevieMP]

I'm going absolutely mad with trying to solve this....if someone could help that would be greatly appreciated.
As per the image attached, the information in column 'E' gets updated (copied in) on an ongoing basis where any new information is added to the next line in column 'E'.
In this instance, I want VBA to update column 'I' if column 'E' has any new data added with 'SP - Email Sent' in the corresponding cell i.e. 'E3' has 'Steve Test8', therefore I want 'I3' to have 'SP - Email Sent'. I would like a timestamp added if possible.

Then the next time I add/copy another Fund in column 'E' e.g. 'Steve Test9', column 'I4' is then updated with 'SP - Email Sent'with a timestamp as well.
The code I have is:

Worksheets("Log").Select
ActiveSheet.Range("I2").Select

Dim count As Integer
count = 1

'skip all used cells
Do While Worksheets("Log").Range("I" & count).Value <> ""
'<>"" means "is not empty", as long as this happens we go down looking for empty cell
If Range("E2").Value <> "" Then
Range("I2").Value = "SP - Email Sent"
End If

count = count + 1
Loop

The code is only populating 'I2' only.
Can someone please please help?

Many thanks.
Steve

 

[Joe4]

A few issues:
1. Your loop is hard-coded to keep checking "E2" and "I2". You are not changing that range.
2. Never use reserved words like "count" for variables! This can cause errors and unexpected results.

I would recommend finding the last row in column E with data, and looping throw all the rows to there, like this:

    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") <> "" Then Cells(r, "I") = "SP - Email Sent"
    Next r

 

[mumps]

See if this approach works for you. Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your "Log" sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter a Fund in any cell in column E and press the RETURN key.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Column <> 5 Then Exit Sub
    Target.Offset(, 4) = "SP - Email Sent " & Now
End Sub

 

[Joe4]

In my haste, I was focusing on the code and glossed over the explanation too quickly.
You would only use a loop if you were running it against existing data.
If you wanted it to update as data is being entered, and have a timestamp added, you would need to use a solution like mumps presented.

That being said, I do offer some advice that you will want to follow regarding variables, and should you ever need to use loops.
So you can file that information away for another day.

 

[StevieMP]

Thank you Mumps and Joe4.
That works indeed, however is there a way to not over-write the 'SP - Email sent' & now when first entered?
As I run the code again it amends the 'SP - Email sent' & now with a newer timestamp.

 

[mumps]

Are you referring to the code I suggested?

 

[Joe4]

Just amend mumps code like this:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Column <> 5 Then Exit Sub
    If Target.Offset(, 4) = "" Then Target.Offset(, 4) = "SP - Email Sent " & Now
End Sub

which will then only update that column if it does not already contain an entry.

Note that you should not be running any code manually. This is automated VBA code that runs whenever column E is being updated.

 

[StevieMP]

Are you referring to the code I suggested?

Yes, both you and Joe's code

 

[Joe4]

Yes, both you and Joe's code

See my last comment.

Based on your original question, I believe that you should only need mump's code, not mine, as it seems you want it to run as data is being entered (and not on data that was already entered in the past).
If you look at my last post, I added the change I think you need to mump's code to do what you require.

 

[StevieMP]

See my last comment.

Based on your original question, I believe that you should only need mump's code, not mine, as it seems you want it to run as data is being entered (and not on data that was already entered in the past).
If you look at my last post, I added the change I think you need to mump's code to do what you require.

Hi Joe4,
I have added your change which works fine, however I have already created code to generate and save a spreadsheet and another bit of code to then take that spreadsheet and add it to an email. At the point of creating the spreadsheet the name e.g. 'Steve TEST8' gets added to the 'Log' sheet. I have a button to generate the email and load the spreadsheet just created into the email. It is at that point I want the code to say in column 'I' 'SP - Email sent', hence my request.
If you could help further that would help greatly.
Thanks in advance.
Steve