PDA

View Full Version : Solved: Validating columns using Formula or Select Case ?



expfresocspy
12-27-2011, 02:51 AM
Hello !

I'm stucked again ):
Anyway , I'm suppose to refer to column H to see if it is an invalid date and if it is an invalid date , I have to indicate " "(blank) in column I . If it is a valid date , I have to indicate "Yes" in column I . This is what my code looks like but it cannot work :/


With Range("I2:I" & Range("H" & Rows.Count).End(xlUp).Row)
.Formula = "=IF(OR(D2=""between #1 Jan 1990# And #31 Dec 2009#""),""Yes"","" "")"
.Value = .Value
End With


Should I do the above or the one below ? :-

For Each rCell1 In rData1.Cells
Select Case rCell1.Value
Case "1/1/1990", "1/1/1990 8:00:00 PM"
rCell1.Offset(0, 1).Value = " "
Case Else
rCell1.Offset(0, 1).Value = "Yes"
End Select
Next

Thanks for the help ! :D : pray2:

Bob Phillips
12-27-2011, 04:10 AM
With Range("I2:I" & Range("H" & Rows.Count).End(xlUp).Row)
.Formula = "=IF(AND(H2>DATE(1990,1,1),H2<= DATE(2009,12,31)),""Yes"","""")"
.Value = .Value
End With

expfresocspy
12-30-2011, 01:35 AM
With Range("I2:I" & Range("H" & Rows.Count).End(xlUp).Row)
.Formula = "=IF(AND(H2>DATE(1990,1,1),H2<= DATE(2009,12,31)),""Yes"","""")"
.Value = .Value
End With


It worked ! thankyou so much :D