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]

So, are you saying that you want column I to be updated NOT necessarily when column E is updated, but when you click this other button to send the emails?
If that is the case, then it would swing back to my code reply instead of mumps (which would run as column E is being updated).

So, if you wanted to update my original code so aas not to overwrite values already existing in column I (and add a timestamp to the end of it), it would look something 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") <> "") And (Cells(r, "I") = "") Then Cells(r, "I") = "SP - Email Sent " & Now
    Next r

 

[StevieMP]

So, are you saying that you want column I to be updated NOT necessarily when column E is updated, but when you click this other button to send the emails?
If that is the case, then it would swing back to my code reply instead of mumps (which would run as column E is being updated).

So, if you wanted to update my original code so aas not to overwrite values already existing in column I (and add a timestamp to the end of it), it would look something 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") <> "") And (Cells(r, "I") = "") Then Cells(r, "I") = "SP - Email Sent " & Now
    Next r

 

[StevieMP]

That's exactly it Joe4 yes.....I'm losing the plot here! I'm getting compile error for one of the brackets and I can't seem to correct it. I have copied your exact code.

 

[Joe4]

That's exactly it Joe4 yes.....I'm losing the plot here! I'm getting compile error for one of the brackets and I can't seem to correct it. I have copied your exact code.

Please post the ENTIRE procedure that you placed it into, so I can see what your whole block of code looks like.

 

[StevieMP]

That's exactly it Joe4 yes.....I'm losing the plot here! I'm getting compile error for one of the brackets and I can't seem to correct it. I have copied your exact code.

I believe the code should read :
If (Cells(r, "E") <> "") And (Cells(r, "I") = "") Then Cells(r, "I") = "SP - Email Sent " & Now

the ( was before If.

 

[Joe4]

I believe the code should read :
If (Cells(r, "E") <> "") And (Cells(r, "I") = "") Then Cells(r, "I") = "SP - Email Sent " & Now

the ( was before If.

Whoops, you are correct.
Typo on my part.
Sorry about that!

Just testing you to see if you were paying attention!

 

[StevieMP]

Whoops, you are correct.
Typo on my part.
Sorry about that!

Just testing you to see if you were paying attention!

Thanks Joe4 - that works perfectly. Thank you for your help, really appreciate it.

 

[Joe4]

You are welcome.
Glad I was able to help.

I went back and edited my typo, so if anyone uses that code now, it will be good.