Help with logic - Check if the order was made too far in advance

Asked

Viewed 101 times

1

I’m developing a script in vba, in the Outlook, so that it saves all attachments that arrive with the new emails, with extension xlsx. It checks if it is the right attachment (through a code that the worksheet will have), copies all the contents of this Workbook (if it is correct) to one another and sends a reply automatically, to the sender of the email. All this is working perfectly.

In column G, of this spreadsheet that arrives by email, there is a list with the months (which represents the person’s birthday month). If the request comes more than two months in advance, I would like the automatic response to have an observation for that request.

For example: We are in the month of December. Requests can only be made for the months of December, January and February. So if, on any line of the spreadsheet, you have a request for months other than these, the automatic reply text would have an added snippet.

That’s the problem. I’m not able to think of a logic that does this check, correctly.

Edit: Below I wrote an excerpt of code very close to what I’m using (the logic is the same, I just don’t have it here):

Dim mesHoje As Integer, mesSolicitacao As Integer    

...
For i=6 To ultimaLinha
        If Plan1.Cells(i, "G") = "Janeiro" Then
           mesSolicitacao = 1
        ElseIf Plan1.Cells(i, "G") = "Fevereiro" Then
           mesSolicitacao = 2

          ...

        ElseIf Plan1.Cells(i, "G") = "Dezembro" Then
           mesSolicitacao = 12
        End If


        mesHoje = Month(Date)


        If Abs((mesHoje + 2) - mesSolicitacao) <= 2 Then '<~ Esse if que eu não estou conseguindo pensar em uma forma de fazer, do jeito que eu quero. Talvez com um if só não da.
            'Rotina para solicitação feita dentro do prazo
        Else
            'Rotina para solicitação feita fora de prazo
        End If
Next

Edit: I believe I found the solution (I did not do all the tests, but of the many I did, in the excel same, they worked out). I only changed the if by the code below. But it would have a more "clean" way of writing this excerpt?

If mesHoje < 11 Then
    If ((mesHoje + 2) - mesSolicitacao) <= 2 And ((mesHoje + 2) - mesSolicitacao) > (-1) Then
        'Rotina para solicitação feita dentro do prazo
    Else
        'Rotina para solicitação feita fora do prazo
    End If
ElseIf mesHoje = 11 Then
    If (mesHoje = 11 And mesSolicitacao = 11) Or (mesHoje = 11 And mesSolicitacao = 12) Or (mesHoje = 11 And mesSolicitacao = 1) Then
        'Rotina para solicitação feita dentro do prazo
    Else
        'Rotina para solicitação feita fora do prazo
    End If
ElseIf mesHoje = 12 Then
    If (mesHoje = 12 And mesSolicitacao = 12) Or (mesHoje = 12 And mesSolicitacao = 1) Or (mesHoje = 12 And mesSolicitacao = 2) Then
        'Rotina para solicitação feita dentro do prazo
    Else
        'Rotina para solicitação feita fora do prazo
    End If
End If
vba logic
  • 3

    What you need then is a function that receives a month as a string and returns true if this month is not more than two months and false otherwise?

  • exactly, @Pablo. I tried to make a if, to verify this. But it did not always work. It went wrong, mainly, in the months of November and December

  • You can enter the code you already have?

  • Code added to question

  • I have thought of several ways here to make your code smaller, but this has affected clarity. What I recommend is just to separate it into functions. For example, create the function estaDentroDoPrazo that gets a month and returns a Boolean. This will help make it clearer.

  • Thanks for the suggestion, @Pablo. I am changing to functions, as I had already done, with the routing of forwarding emails.

Show 1 more comment
No answers

Browser other questions tagged vba logic

You are not signed in. Login or sign up in order to post.