If clause, multiple conditions

[Alex24]

Hello everyone,

I am trying to write a command that uses very much the if clause. Several conditions have to be made in order for the data to be moved therefore I've used multiple clauses. The problem is that when I try to run each line of the code I immediately notice that the code does not walk through all the clauses although it should because the data satisfies the specified conditions.

For a better understanding I will attach my test file and the command I wrote. Before the code in question I use two other short commands that sort the data and copy one column in order for the code to recognize the date format (the output that I receive cannot be recognized by the command). If you know any other methods of dealing with these first two steps I will greatly appreciate any suggestion.

Hope my explanation is clear. Thanks in advance for your advice.

Kind regards

 

[Asheesh]

Hi Alex -

What are you trying to achieve with this if condition...Appreciate if you could explain..

 

[chirayu]

This what you want?

Sub Status()

'Application.ScreenUpdating = False

Dim rowmax As Integer, crtrow As Integer, today As Double

today = Date
rowmax = Sheets("Orderbook").Cells(2, 1).End(xlDown).Row
crtrow = 2

While crtrow < rowmax + 1
   
   
    'Part1
    '- If Cell Date less than Today
    '- And Order Status is Open
    '- Then Change Order Status to Available
    Range("F" & crtrow).Select
    If Sheets("Orderbook").Range("F" & crtrow).Value = " " Then
    ElseIf Sheets("Orderbook").Range("F" & crtrow).Value <= today _
    And Sheets("Orderbook").Range("D" & crtrow).Text = "Open" Then
   
        Sheets("Orderbook").Range("D" & crtrow) = "Available"
    End If
   
        'Part2:
        '- If Cell Date less than Today
        '- And Order Status is Available
        '- And Unasgnd Qty is Greater than 0
        '- Then Copy Unasgnd Qty to Hard Rsrvd Qty
        '- Change Unasgnd Qty to 0
        Range("F" & crtrow).Select
        If Sheets("Orderbook").Range("F" & crtrow).Value = " " Then
       
        ElseIf Sheets("Orderbook").Range("F" & crtrow).Value <= today _
        And Sheets("Orderbook").Range("D" & crtrow).Text = "Available" _
        And Sheets("Orderbook").Range("A" & crtrow).Value >= 0 Then
       
            Sheets("Orderbook").Range("A" & crtrow).Copy
            Sheets("Orderbook").Range("B" & crtrow).PasteSpecial xlPasteValues
            Sheets("Orderbook").Range("A" & crtrow).Value = 0
        End If
   
            'Part3:
            '- If Cell Date less than Today
            '- And Order Status is Available
            '- And Unasgnd Qty is Less than 0
            '- Then Change Hard Rsrvd % to 100%
            '- Change Hard Rsrvd Qty to 0
            Range("F" & crtrow).Select
            If Sheets("Orderbook").Range("F" & crtrow).Value = " " Then
           
            ElseIf Sheets("Orderbook").Range("F" & crtrow).Value <= today _
            And Sheets("Orderbook").Range("D" & crtrow) = "Available" _
            And Sheets("Orderbook").Range("B" & crtrow).Value > 0 Then
               
                    Sheets("Orderbook").Range("C" & crtrow).Value = "100%"
           
            End If

                crtrow = crtrow + 1
   
Wend
   
    Columns("F").EntireColumn.Delete
    MsgBox Prompt:="Change status command has been completed", Buttons:=vbInformation
   
Application.ScreenUpdating = True
End Sub

 

[Alex24]

Hello again,

Thank you for showing interest in helping me with this issue. I forgot to explain what I am trying to achieve with the code, it might be too unclear in some parts.

The logic behind the code, as chirayu pointed out, is the following:

The subrutine should check if the release date is today or in the past and if the corresponding status is open. If these conditions are satisfied then the status of these lines should change in available.
Following, it will move the values in the unasigned quantity to hard reserved. Then it will set the unasigned quantity to zero (for the previously moved values) and finally it will change hard reserverved % to 100%.

With chirayu's suggestion I managed to make the code respect all the ifs, however the problem now is that it takes into considerations all the cells under the release date column and I want it to ignore the ones that are not populated.

I have tried with the following:

Range("F" & crtrow).Select
If Sheets("Sheet1").Range("F" & crtrow) Is Nothing Then
Sheets("Sheet1").Range("F" & crtrow).Value = "Blank"
ElseIf Sheets("Sheet1").Range("F" & crtrow).Value <= today _
And Sheets("Sheet1").Range("A" & crtrow).Text = "Open" Then

Sheets("Sheet1").Range("A" & crtrow) = "Available"
End If

But instead of omitting those cells it seems that the subrutine is taking into consideration only the blank cells.

Any advice would be more than welcome. I think I am very close to finalizing it, but yet very far because of this issue.

Thanks for the previous advice, it helped me correct and add to my knowledge about macros.

Kind regards

 

[chirayu]

I'd personally avoid the "Nothing" statement. Given examples you can use below depending on requirement:


To consider blank cells

'Use this:
If IsEmpty(Range("F" & crtrow)) Then

'Or this:
If Range("F" & crtrow) = "" Then

To not consider blank cells

'Use this:
If Not IsEmpty(Range("F" & crtrow)) Then

'Or this:
If Not Range("F" & crtrow) = "" Then

 

[Alex24]

Hello again,

I've managed to get around this with your help. However, I've discovered that the problem lies when comparing the date in the output with the date of today (which is taken from the system). The format of the output is dd.mm.yyyy and I need it in mm/dd/yyyy in order for the command to take it into consideration.

I have tried the following:
Sheet1.Range("E" & crtrow).NumberFormat = "dd/mm/yyyy"
Range("E" & crtrow).Value = Format("dd/MM/yyyy")

The methods above do not work. The code simply ignores the instructions. I've manually changed one of the dates into the format needed and the rest of the command works perfectly. Do you have any advice on how can I deal with this new, however initial, issue.

Thanks in advance!

Kind regards

 

[chirayu]

Probably the DD.MM.YYYY is being considered as text rather than date so you'll need to set your value as a variable. You don't need to convert it to MM/DD/YYYY format for it to be considered. Sample code below

Dim MyDate As Date

MyDate = _
Left(Range("E" & crtrow).Value, 2) & "-" & _
MonthName(Mid(Range("E" & crtrow).Value, 4, 2)) & "-" & _
Right(Range("E" & crtrow).Value, 4)

 

[Alex24]

Thank you all for your advice. The macro works.

Best regards,

Alex