VBA code to range of days Closed

Question

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 System Configuration: Windows / Chrome 61.0.3163.13

ac3mark · Answer

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=1SKIPIT=trueend ifif SKIPIT=true then goto NEXTCHECKIf thisvariable <=0 then variableOne=0SKIPIT=trueend ifif SKIPIT=true then goto NEXTCHECK:nextcheckHere 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

eunice3301 · Answer

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

VBA code to range of days

2 responses

Similar discussions

Subscribe To Our Newsletter!