looping results

[wilg]

Hi, have a piece of code below that I want to return results if from rows 4 to 64 in column b have a date that is today or less and then return the results of column b with column j on the same line.

[VBA]For i = 2 To 64


If Cells(4, i).Value <= now() Then

Email_Body = Email_Body & vbNewLine & Cells(4, 2).text & Cells(4, 10).Value
End If

Next i[/VBA]

the code gives me the results of row 4 column b and column j 64 times. What do I need to do differnt to stop it if it doesnt find the date that is today or less than today?

I am using this along with an send email code. But its

[Kenneth Hobs]

Without more detail, it is hard to help. You should set a value for Now() rather than recomputing it for each loop. Making some guesses:

[VBA]For i = 2 To 64


If IsDate(Cells(4, i).Value) and (Cells(4, i).Value2 <= CDbl(Now)) Then

Email_Body = Email_Body & vbNewLine & Cells(4, 2).Text & Cells(4, 10).Value
'Exit For, or Goto, or Exit Sub, or ...
End If

Next i[/VBA]

[wilg]

Your code works in returning results in row 4, but does not continue to loop down through rows 64 if the date is today or less than today.

[Kenneth Hobs]

If you make an example xls or xlsm, it is easier to help.

When you used Now() that includes the time. When you compare dates that are equal, it unlikely that any date would be equal unless the time you ran it was exactly midnight. Compare to CDBl(Date) if time is not needed.

Why would the code go to any other row other than row 4? Your code is iterating through the columns, not rows. You have also hard coded the value of Email_Body so if any cell in row 4 to columns 2 to 64 meets the IF() then you get the same value.

[wilg]

Hi, I have multiple dates in rows 4 to 64 and would like to email these results. The code above emails just the first result in row 4, but I need it to return any instance of dates less than today.

[VBA]Dim Email_Subject, Email_Send_From, Email_Send_To, _
Email_Cc, Email_Bcc, Email_Body As String
Dim Mail_Object, Mail_Single As Variant
Email_Subject = "UpdateEmail_Send_From = ""
Email_Send_To = ""
Email_Cc = ""
Email_Bcc = ""
Email_Body = vbNewLine & "Update"
On Error GoTo debugs
Set Mail_Object = CreateObject("Outlook.Application")
Set Mail_Single = Mail_Object.CreateItem(0)
With Mail_Single
.Subject = Email_Subject
.To = Email_Send_To
.cc = Email_Cc
.BCC = Email_Bcc
.Body = Email_Body
For i = 2 To 64


If IsDate(Cells(4, i).Value) And (Cells(4, i).Value2 <= CDbl(Date)) Then

Email_Body = Email_Body & vbNewLine & Cells(4, 2).Text & Cells(4, 10).Value
'Exit For, or Goto, or Exit Sub, or ...
End If

Next i
.Body = Email_Body
.send
End With

End Select
[/VBA]

[wilg]

The below code works for me but I would neeed to copy this and change each instance to one row more 64 times. I would like the code to loop as opposed to me changing each instances row by one.


[VBA]If Cells(4, 2).Value <= Now() Then

Email_Body = Email_Body & vbNewLine & Cells(4, 2).Text & Cells(4, 10).Value
End If

If Cells(5, 2).Value <= Now() Then

Email_Body = Email_Body & vbNewLine & Cells(5, 2).Text & Cells(4 + 1, 10).Value
End If

.Body = Email_Body
[/VBA]

[Kenneth Hobs]

[VBA] Dim i As Long, Email_Body As String
For i = 4 To 68
If Cells(i, 2).Value <= Now() Then
Email_Body = Email_Body & vbNewLine & Cells(i, 2).Text & Cells(i, 10).Value
End If
Next i
[/VBA]

[wilg]

Good job! Thats it. Thanks so much Kenneth.