VBA code to range of days

[Closed]
Report
Posts
13
Registration date
Thursday July 27, 2017
Status
Member
Last seen
August 29, 2017
-
 Blocked Profile -
Hello,

Please help guys...

I have a code below:

Sub Auto_Open()
Dim r As Long
    For r = 2 To 53
        If Range("I" & r).Value <= 0 And Range("L2").Value >= Range("I" & r).Value Then
            MsgBox Range("B" & r).Value & " EID has Expired"
        End If
    Next r
    For r = 2 To 53
        If Range("I" & r).Value <= 30 And Range("L2").Value >= Range("I" & r).Value Then
            MsgBox Range("B" & r).Value & " EID almost expired"
        End If
    Next r
    For r = 2 To 53
        If Range("J" & r).Value <= 0 And Range("L2").Value >= Range("J" & r).Value Then
            MsgBox Range("B" & r).Value & " VISA has Expired"
        End If
    Next r
    For r = 2 To 53
        If Range("J" & r).Value <= 30 And Range("L2").Value >= Range("J" & r).Value Then
            MsgBox Range("B" & r).Value & " VISA almost expired"
        End If
    Next r
    For r = 2 To 53
        If Range("K" & r).Value <= 0 And Range("L2").Value >= Range("K" & r).Value Then
            MsgBox Range("B" & r).Value & " PASSPORT has expired"
        End If
    Next r
    For r = 2 To 53
        If Range("K" & r).Value <= 0 And Range("L2").Value >= Range("K" & r).Value Then
            MsgBox Range("B" & r).Value & " PASSPORT almost expired"
        End If
    Next r
End Sub



For the ALMOST EXPIRED, HOW CAN I CHANGE THE RANGE INTO 1-30 DAYS? SO THAT ONLY BELOW <= 0 WILL ONLY APPEAR "EXPIRED" AND THE = 1-30 WILL APPEAR "ALMOST EXPIRED"

Because in my code <= 0 will appear twice as "expired" and "almost expired"

Please i need help.


Thank you so much

EDIT : Adding code tags (syntactic colors).

Please, do not forget them in your next messages

2 replies

OK, so you could have a BOOLEAN variable of SKIPIT. Initialize SKIPIT as boolean. Set SKIPIT as false. If the first logic test is true, have it change skipit to true. If skipit is true, have it jump to the next logic step, bypassing the duplicate check for EXPIRED, as we know it is almost expired. Be certain to re-initialize skiptit in between each logic test!


Something like:

If thisvariable <=30 then 
variableOne=1
SKIPIT=true
end if
if SKIPIT=true then goto NEXTCHECK

If thisvariable <=0 then
variableOne=0
SKIPIT=true
end if
if SKIPIT=true then goto NEXTCHECK

:nextcheck
Here is the rest of the code that was run instead of checking on the variableOne!


It's kind of fun to do the impossible! -Walter Elias Disney
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Posts
13
Registration date
Thursday July 27, 2017
Status
Member
Last seen
August 29, 2017

Hello Acemark,

Thank you so much for the help but i don't know how to use your code =)
I am very new in vba and macro and i have zero knowledge. I just manage to find the code in the web and modify it and gladly it works =) ... please be patient with me, can you explain where can i add the code you have suggested in my existing code? Please help.

Thank you so much =)
Posts
13
Registration date
Thursday July 27, 2017
Status
Member
Last seen
August 29, 2017

Hello Acemark,

Got the code...=) i just added this in the code

For r = 2 To 53
If Range("I" & r).Value <= 30 And Range("I" & r).Value >= 1 And Range("L2").Value >= Range("I" & r).Value Then
MsgBox Range("B" & r).Value & " EID almost expired"
End If

It works for now hehehe =)

Thank you
Blocked Profile
OK, that will work. Typically you would want to shy away from nested AND's, but if you know what it is doing! Rite ON! Always come back, and we will help when we can!