VBA code to range of days

eunice3301 13 Posts Thursday July 27, 2017Registration date August 29, 2017 Last seen - Aug 1, 2017 at 07:47 AM - Latest reply: ac3mark 9999 Posts Monday June 3, 2013Registration dateModeratorStatus July 20, 2018 Last seen
- Aug 3, 2017 at 04:33 PM
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

See more 

Your reply

4 replies

Best answer
ac3mark 9999 Posts Monday June 3, 2013Registration dateModeratorStatus July 20, 2018 Last seen - Updated by ac3mark on 1/08/17 at 04:51 PM
1
Thank you
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

Thank you, ac3mark 1

Something to say? Add comment

CCM has helped 1662 users this month

eunice3301 13 Posts Thursday July 27, 2017Registration date August 29, 2017 Last seen - Aug 2, 2017 at 02:28 AM
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 =)
Respond to ac3mark
eunice3301 13 Posts Thursday July 27, 2017Registration date August 29, 2017 Last seen - Aug 3, 2017 at 09:48 AM
0
Thank you
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
ac3mark 9999 Posts Monday June 3, 2013Registration dateModeratorStatus July 20, 2018 Last seen - Aug 3, 2017 at 04:33 PM
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!
Respond to eunice3301