VBA If Statements between dates

[JohnDoe2022]

Hello,

I am having trouble with constructing an If statement between dates. I want the template to place a note if column "A"(Date of Birth) has #N/A or if the termination date is less than the end of the current month to place a note in column E that says "Termed" and if column "A"(Date of Birth) is over age 21 to place a note in column "E" to say "Over 21". I want the macros to run thru all cells till empty because each time the report is run, there will be a different number of lines. I have reference cells for the End of Current month and the date that will be over 21. Each month the report is run, those dates will change. I am not very familiar with VBA, and it is probably really easy, but I am having a hard time.

This is what I have so far; I know I am probably completely off.

Sub Over21()

Dim sDate As Long
Dim Eom As Long
Eom = Range("H2").Value
sDate = Range("I2").Value
For i = 2 To 1500

If Worksheets("Sheet1").Cells(i, 1).Value = "#N/A" Then
Worksheets("Sheet1").Cells(i, 5).Value = "Termed"

ElseIf Worksheets("Sheet1").Cells(i, 3).Value <= Eom Then
Worksheets("Sheet1").Cells(i, 5).Value = "Termed"

ElseIf Worksheets("Sheet1").Cells(i, 1).Value <= sDate Then
Worksheets("Sheets1").Cells(i, 5).Value = "Over 21"
End If
Next

End Sub

Any help is very much appreciated!!

 

[SeniorNewbie]

Hello John,

honestly I'm a little bit confused about the purpose for your formula because there are some conditions less then and greater then I do not understand.

Let me try say it in my words: You've got the date of birth of a person und want to know when he's elder than 20 years? correct? In a second step you want to write in column 5/E a marks like Termed and Over 21. Correct?

Okay:
For step 1 use this formulas

• in Column 2/B =A1 + (365*7) + 5. What's behind? normal year are 365 days, 21 years include (if it's not around years ending on xx00) 5 leap years. The result should be correct
• in Column 3/C =if(B1<2560;"too young";"old enough")

For step 2

• in Column D/4 the Termination Date is given
• in Column E/5 =if(Date(now) >D1;"term";"")

If you don't want to copy this formulas to every new line, I poropose a loop function in VBA to help. But Before I do so, I should know, if my guessings are correct.

Hello from Germany
Senior

 

[JohnDoe2022]

Hello John,

honestly I'm a little bit confused about the purpose for your formula because there are some conditions less then and greater then I do not understand.

Let me try say it in my words: You've got the date of birth of a person und want to know when he's elder than 20 years? correct? In a second step you want to write in column 5/E a marks like Termed and Over 21. Correct?

Okay:
For step 1 use this formulas

• in Column 2/B =A1 + (365*7) + 5. What's behind? normal year are 365 days, 21 years include (if it's not around years ending on xx00) 5 leap years. The result should be correct
• in Column 3/C =if(B1<2560;"too young";"old enough")

For step 2

• in Column D/4 the Termination Date is given
• in Column E/5 =if(Date(now) >D1;"term";"")

If you don't want to copy this formulas to every new line, I poropose a loop function in VBA to help. But Before I do so, I should know, if my guessings are correct.

Hello from Germany
Senior

Hi,

I'm sorry I did not explain it right. I want to perform 3 If statements. One is if the date of birth in column "A" is greater than or equal to the date in column"I2", indicating the person will be or over 21, place a note in Column "E" stating "Over 21" if not, leave it blank. The next is if the date in column "C" is equal to or less than the date in column"H2"; place a note in Column "E" stating "Termed"; if not, leave it blank. And lastly, if Column "A" has a "#N/A" place a note in Column "E" stating "Termed", if not leave it blank. This report will be done every month, so the Dates in columns "H2" and "I2" will change each time. I have a formula in columns "H2" and "I2" that will automatically update each time report is opened.

I hope that is a better explanation
Thank you

 

[SeniorNewbie]

Hi John,

thanks for the further explaination. Okay - now I see more and I'm still thinking, the best way to handle it, is usind a loop in VBA. I would use a cascading Select Case ... to filter/edit the coloumns. The event to run the code can be various. The best benefit is: No need to copy any formula to the next row. The function will just edit tall rows.

I'll do some tests later and let you know.

CU Senior