what am i doing wrong???

[powercell99]

Hi, i cant figure out what i am doing wrong. Trying to look at a range and test if date value in cell is greater than date value in another cell, if so then enter text in another cell, then go to the next cell and perform the same test, do this till the last row of data in that column. But when i run the code, it skips rows. What am i doing wrong? Below is the result i get when i run the code....

Col"A" Col"B " Col"C" Col"D" Col"E"
[TABLE="width: 350"]
<tbody>[TR]
[TD][TABLE="width: 428"]
<tbody>[TR]
[TD]green[/TD]
[TD]Honda[/TD]
[TD="align: right"]4/1/2015[/TD]
[TD]Project1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]blue[/TD]
[TD]Nissan[/TD]
[TD="align: right"]4/1/2015[/TD]
[TD]Project 1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]red[/TD]
[TD]Ford[/TD]
[TD="align: right"]6/1/2015[/TD]
[TD]Project 2[/TD]
[TD]Date Newer[/TD]
[/TR]
[TR]
[TD]red[/TD]
[TD]Chevy[/TD]
[TD="align: right"]6/1/2015[/TD]
[TD]Project 3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]green[/TD]
[TD]Honda[/TD]
[TD="align: right"]6/1/2015[/TD]
[TD]Project 2[/TD]
[TD]Date Newer[/TD]
[/TR]
[TR]
[TD]green[/TD]
[TD]Nissan[/TD]
[TD="align: right"]6/1/2015[/TD]
[TD]Project 1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]black[/TD]
[TD]Ford[/TD]
[TD="align: right"]6/1/2015[/TD]
[TD]Project 4[/TD]
[TD]Date Newer[/TD]
[/TR]
[TR]
[TD]orange[/TD]
[TD]Chevy[/TD]
[TD="align: right"]6/1/2015[/TD]
[TD]Project 5[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Where am i going wrong??? I use the debug.print often in the code to test as i am building, sorry if its overused. Any help or tips you can provide would be GREATLY appreciated. Thanks [/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Larry[/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Sub testdate()


Dim WSD As Worksheet
Dim LastRow As Long
Dim OrigDate As Long
Dim StartDate As Long

Set WSD = Worksheets("Data")
LastRow = WSD.Range("A10000").End(xlUp).Row
    
    Debug.Print LastRow

Dim i As Integer
For i = 1 To (LastRow)
    Debug.Print i

WSD.Activate
OrigDate = DateValue(Range("C" & i).Value)
StartDate = DateValue(Range("J1").Value)
    Debug.Print OrigDate
    Debug.Print StartDate

If OrigDate > StartDate Then
        Range("E" & i).Value = "Date Newer"
    
        Debug.Print Range("E" & i).Value
 
End If
       i = i + 1
Next i


End Sub

 

[Marcelo Branco]

Erase this code line (after End If)
i = i +1


It is not necessary because the For already increases the value of the control variable (i in your case) by 1 when Step is omitted.

See the Help file

M.

 

[circledchicken]

Hi,

I think you can remove the 'i = i+1' line near the end, as the increment is carried out by the 'Next i' part.
E.g. something like this:

Sub testdate()


    Dim WSD         As Worksheet
    Dim LastRow     As Long
    Dim StartDate   As Long
    Dim i           As Long
    
    Set WSD = Worksheets("Data")
    LastRow = WSD.Range("A" & WSD.Rows.Count).End(xlUp).Row
    
    StartDate = DateValue(WSD.Range("J1").Value)
    For i = 1 To LastRow
        If DateValue(WSD.Range("C" & i).Value) > StartDate Then
            WSD.Range("E" & i).Value = "Date Newer"
        End If
    Next i


End Sub

 

[powercell99]

Awesome Marcelo. That works! Thanks very much for the very fast response and help. I appreciate it!

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

M.

 

[powercell99]

Thanks for the explanation circledchicken. It makes sense now

 

[circledchicken]

Thanks for the explanation circledchicken. It makes sense now

You're welcome!