whats wrong here? (poor "For Each Next" code?)

[diamantebonita]

Could someone tell me whats wrong here? The code is supposed to go through a list of dates, and any that are either today or within the week, its supposed to copy the related cells and paste in a separate area of the spreadsheet.

Private Sub Workbook_Open()
Dim msg As String
Dim vendorL As String
Dim amountL As String
Dim duedateL As String
Dim FinalRow As Integer
Dim TodayDate As String
Dim Cell As Range



Sheets("Bills").Activate
Range("G2").Activate
FinalRow = Cells(Rows.Count, 7).End(xlUp).Row

For Each Cell In Range("G2:G" & FinalRow)
If Cell.Value >= Date And Cell.Value < Date + 7 Then
Range(ActiveCell.Offset(0, -2), ActiveCell).Copy
Range("AA1").Select
If Cell.Value = "" Then
Selection.PasteSpecial Paste:=xlPasteValues
Else
Do
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell.Value = ""
Selection.PasteSpecial Paste:=xlPasteValues
End If
Else
ActiveCell.Offset(1, 0).Select
End If
Next Cell



Any help would be appreciated. Thanks!

 

[tweedle]

What is it doing/not doing that you want to change?

or within the week

What does this mean exactly? Within the next 7 days?

 

[stnkynts]

Following the format of your code

If Cell.Value = "" Then

Should be

If Selection.Value = "" Then

Also not sure if the whole Date + 7 is gonna work correctly. I'll test it out and get back to you.

 

[stnkynts]

Guess the Date + 7 worked

Try this and let me know how close it is.

Sub Bills()
Dim ws1 As Worksheet:   Set ws1 = Sheets("Bills")
Dim lastrow As Long
Dim icell As Range
 
lastrow = ws1.Range("G" & Rows.Count).End(xlUp).Row
 
For Each icell In ws1.Range("G2:G" & lastrow)
    If icell.Value >= Date And icell.Value < Date + 7 Then
        icell.Offset(0, -2).Copy Destination:=ws1.Range("AA" & Rows.Count).End(xlUp).Offset(1, 0)
    End If
Next icell
 
End Sub

 

[diamantebonita]

Basically its not checking through the range given to find ALL the dates that fit the criteria. i want it to check for any date that is today and within a week from today and paste it in separate area of the spreadsheet, making a list.

To test it out, I put a few dates in the range. Two of those dates i expected to be copied and pasted to the list. But it only copied and pasted one, it didnt go through the entire range.

 

[diamantebonita]

stnkynts, Thanks alot! that worked perfectly!