Short circuiting logical expressions?

[jennyc2]

Hi everyone!

I have a problem with a part of my program. I would like to execute the second expression in this statement:

If AndAlso(ActiveCell.Offset(i - 1, 2) <> "", DateValue(ActiveCell.Offset (i - 1, 2)) < DateValue(Today)) Then
ActiveCell.Offset(i - 1, -7).Value = "4"

ONLY if the first expression is not true. Since excel seems to execute both statements in an AND command, I wrote my own AndAlso function:

Public Function AndAlso(expr1 As Boolean, expr2 As Boolean) As Boolean
If expr1 Then
If expr2 Then
AndAlso = True
Else
AndAlso = False
End If
Else
AndAlso = False
End If
End Function

(Sorry for the bad indent!) It doesn't look so nice, so I am figuring that there must be better ways. Also I still get an error message from the DateValue-execution when the cell is empty. Anyone has any ideas?

[arthurbr]

Hi, please take a moment to read forum rules about wrapping your code with tags.

[jennyc2]

Oh right, sorry, here is a new try to post the code:

If AndAlso(ActiveCell.Offset(i - 1, 2) <> "", DateValue(ActiveCell.Offset (i - 1, 2)) < DateValue(Today)) Then
ActiveCell.Offset(i - 1, -7).Value = "4"

Public Function AndAlso(expr1 As Boolean, expr2 As Boolean) As Boolean
If expr1 Then
If expr2 Then
AndAlso = True
Else 
AndAlso = False
End If
Else
AndAlso = False
End If
End Function

[arthurbr]

Ok, thx for adapting. You didn't have to make a new post, just edit your original one, and adapt.

[shg]

Your function receives two logical arguments. The way they got from being expressions to being Booleans is via evaluation. If one of them evaluates to an error, AndAlso never executes.

Skip AndAlso, but use the same construct as you did in AndAlso.

And also (), instead of

DateValue(ActiveCell.Offset (i - 1, 2)) < DateValue(Today))

use

DateValue(ActiveCell.Offset (i - 1, 2)) < Date

[jennyc2]

Thanks for your reply! I removed the function but now there is a different error due to the nested ifs. See, I have three columns (and multiple rows) of information to look through and the cells can be either empty or contain a date, so I have to check each cell first and then calculate if the date has passed or not. If it has passed, then it should change to the next row.

Although the sub continues to check the other columns too and replaces the result with the number of the last non-empty cell. Since an "Exit For" could not solve - what can?

Sub Calculate_phase()
    
    Dim i 'variable for looping in the for loop
Dim w    
w = 8
        
    For i = w To 1 Step -1 
        
ActiveWorkbook.Sheets("Sheet1").Range("P2").Activate 'activate first cell below header
        
                If ActiveCell.Offset(i - 1, 2) <> "" Then
                    If DateValue(ActiveCell.Offset(i - 1, 2)) < Date Then
                        ActiveCell.Offset(i - 1, -7).Value = "4"
                        ' Exit For 'this did not work since it exits the loop completely
                    End If
                End If
                                      
                If ActiveCell.Offset(i - 1, 1) <> "" Then
                    If DateValue(ActiveCell.Offset(i - 1, 1)) < Date Then
                        ActiveCell.Offset(i - 1, -7).Value = "3"
                        ' Exit For 'this did not work since it exits the loop completely
                    End If
                End If
                
                If ActiveCell.Offset(i - 1, 0) <> "" Then
                    If DateValue(ActiveCell.Offset(i - 1, 0)) < Date Then      
                        ActiveCell.Offset(i - 1, -7).Value = "2"
                    Else
                        ActiveCell.Offset(i - 1, -7).Value = "1"
                    End If
                End If    
    Next i
    
End Sub

Thanks in advance for all help possible! This is really tricky for me!

[shg]

I can't tell what you're trying to do. Maybe this:

Sub Calculate_phase()
    Dim i   As Long    'variable for looping in the for loop
    Dim w   As Long
    
    w = 8

    For i = w To 1 Step -1

        Application.Goto ActiveWorkbook.Sheets("Sheet1").Range("P2")

        If ActiveCell.Offset(i - 1, 2) <> "" Then
            If DateValue(ActiveCell.Offset(i - 1, 2)) < Date Then
                ActiveCell.Offset(i - 1, -7).Value = "4"
            End If

        ElseIf ActiveCell.Offset(i - 1, 1) <> "" Then
            If DateValue(ActiveCell.Offset(i - 1, 1)) < Date Then
                ActiveCell.Offset(i - 1, -7).Value = "3"
            End If

        ElseIf ActiveCell.Offset(i - 1, 0) <> "" Then
            If DateValue(ActiveCell.Offset(i - 1, 0)) < Date Then
                ActiveCell.Offset(i - 1, -7).Value = "2"
            Else
                ActiveCell.Offset(i - 1, -7).Value = "1"
            End If
        End If
    Next i
End Sub

Also, if the cells in question contain actual dates (as opposed to text that looks like dates), I'd change the coode to look more like this:

Sub Calculate_phase()
    Dim i       As Long
    Dim w       As Long

    w = 8

    For i = w To 1 Step -1
        Application.Goto ActiveWorkbook.Sheets("Sheet1").Range("P2")

        With ActiveCell
            If .Offset(i - 1, 2).Value <> "" Then
                If .Offset(i - 1, 2).Value < Date Then
                    .Offset(i - 1, -7).Value = "4"
                End If

            ElseIf .Offset(i - 1, 1).Value <> "" Then
                If .Offset(i - 1, 1).Value < Date Then
                    .Offset(i - 1, -7).Value = "3"
                End If

            ElseIf .Offset(i - 1, 0).Value <> "" Then
                If .Offset(i - 1, 0).Value < Date Then
                    .Offset(i - 1, -7).Value = "2"
                Else
                    .Offset(i - 1, -7).Value = "1"
                End If
            End If
        End With
    Next i
End Sub