PDA

View Full Version : Loop through the date



alexandro88
11-01-2011, 10:50 PM
Hi All,

I want to identify the date within which range week.
However, I can't get the actual output.

LValue = 2-Nov-11

Actual sheet:
Column A Column B Column H
29-Oct-11 5-Nov-11
5-Nov-11 12-Nov-11


After run the code i will get :
Column A Column B Column H
29-Oct-11 5-Nov-11 Fail
5-Nov-11 12-Nov-11 Fail

The output i want :
Column A Column B Column H
29-Oct-11 5-Nov-11 Fail
5-Nov-11 12-Nov-11

My code:
Option Explicit

Sub ASM006()

Dim rngcell As Range
Dim LValue As String


LValue = Format(Date, "d - mmm - yy")
Worksheets("LW").Activate

For Each rngcell In Range("A3:G3" & Range("A" & Rows.Count).End(xlUp).Row)

If LValue <= Range("A" & rngcell.Row).Value And Range("B" & rngcell.Row).Value >= LValue Then
Range("H" & rngcell.Row).Value = "Fail"
End If

Next
' End of LOOP
End Sub

mohanvijay
11-02-2011, 12:20 AM
try this




Dim Last_Row As Long, i As Long
Dim Ch1 As Long, Ch2 As Long
Dim WS_Main As Worksheet

Set WS_Main = ThisWorkbook.Worksheets("LW")

With WS_Main
Last_Row = .Cells(Rows.Count, 1).End(xlUp).Row
For i = 3 To Last_Row

Ch1 = DateDiff("d", .Cells(i, 1).Value, Date)
Ch2 = DateDiff("d", Date, .Cells(i, 2).Value)

If Ch1 >= 0 And Ch2 >= 0 Then
.Cells(i, 8).Value = "Fail"
End If

Next i

End With

Set WS_Main = Nothing