Report

VBA code to range of days

Ask a question eunice3301 13Posts Thursday July 27, 2017Registration date August 29, 2017 Last seen - Last answered on Aug 3, 2017 at 04:33 PM by ac3mark
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

Helpful
+1
plus moins
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!


Was this answer helpful?  
eunice3301 13Posts 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 =)
Reply
Leave a comment
Helpful
+0
plus moins
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 8017Posts Monday June 3, 2013Registration date ModeratorStatus October 20, 2017 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!
Reply
Leave a comment

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!