Returning a Value

[NWE]

Hi,

I am trying to figure out how to structure this block of code so that if the values in Cells B4, C4, and D4 are all "pass" then the value in Cell E4 is also "pass" (all three must have pass for E4 to say pass) else the value in E4 is "fail". Thank you for your help!

Sub If_Compare()
'This will compare the input values for Pass/Fail Resultant'
    With Activesheet
        If Activesheet.Range("B3").Value >= 0.6 Then
            Activesheet.Range("B4").Value = "Pass"
        Else
            Activesheet.Range("B4").Value = "Fail"
        End If
        If Activesheet.Range("C3").Value <= 3.5 Then
            Activesheet.Range("C4").Value = "Pass"
        Else
            Activesheet.Range("C4").Value = "Fail"
        End If
        If Activesheet.Range("D3").Value <= 6 Then
            Activesheet.Range("D4").Value = "Pass"
        Else
            Activesheet.Range("D4").Value = "Fail"
        End If
        If Activesheet.Range("B4:C4:d4").Value = "Pass" Then
            Activesheet.Range("E4").Value = "Pass"
        Else
            Activesheet.Range("E4").Value = "Fail"
        End If
    End With
End Sub

[offthelip]

You need to change this line

If Activesheet.Range("B4:C4:d4").Value = "Pass" Then

to

If ActiveSheet.Range("B4") = "Pass" And ActiveSheet.Range("C4") = "Pass" And ActiveSheet.Range("D4") = "Pass" Then

[NWE]

Thanks that got it!

[Paul_Hossler]

Since you have With ActiveSheet, you really don't need it on ActiveSheet properties inside the With / End With

Option Explicit

Sub If_Compare()

'This will compare the input values for Pass/Fail Resultant'
    With ActiveSheet
        .Range("B4").Value = IIf(.Range("B3").Value >= 0.6, "Pass", "Fail")
        .Range("C4").Value = IIf(.Range("C3").Value <= 3.5, "Pass", "Fail")
        .Range("D4").Value = IIf(.Range("D3").Value <= 6, "Pass", "Fail")
        .Range("E4").Value = IIf((.Range("B4").Value = "Pass") And (.Range("B4").Value = "Pass") And (.Range("B4").Value = "Pass"), "Pass", "Fail")
    End With

End Sub

[NWE]

If there is nothing in the cell, should I structure it as:

Option Explicit
Sub If_Compare()
'This will compare the input values for Pass/Fail Resultant'
    With ActiveSheet
        .Range("B4").Value = IIf IsEmpty(.Range("B3") then 'Do nothing 
                             elseif(.Range("B3").Value >= 0.6, "Pass", "Fail")

[Paul_Hossler]

This is one way. There

Option Explicit
Sub If_Compare()
'This will compare the input values for Pass/Fail Resultant'
    With ActiveSheet
        .Range("B4").Value = IIf(Len(.Range("B3")) > 0, IIf(.Range("B3").Value >= 0.6, "Pass", "Fail"), "")
        .Range("C4").Value = IIf(Len(.Range("C3")) > 0, IIf(.Range("C3").Value <= 3.5, "Pass", "Fail"), "")
        .Range("D4").Value = IIf(Len(.Range("D3")) > 0, IIf(.Range("D3").Value <= 6#, "Pass", "Fail"), "")
        .Range("E4").Value = IIf((.Range("B4").Value = "Pass") And (.Range("B4").Value = "Pass") And (.Range("B4").Value = "Pass"), "Pass", "Fail")
    End With
End Sub

A slightly more wordy, but more straight-forward, way

Sub If_Compare_Alt()
    Dim n As Long
    
    With ActiveSheet
        If Len(.Range("B3")) = 0 Then
            .Range("B4").Value = vbNullString
        ElseIf .Range("B3").Value >= 0.6 Then
            .Range("B4").Value = "Pass"
            n = n + 1
        Else
            .Range("B4").Value = "Fail"
        End If
        
        If Len(.Range("C3")) = 0 Then
            .Range("C4").Value = vbNullString
        ElseIf .Range("C3").Value <= 3.5 Then
            .Range("C4").Value = "Pass"
            n = n + 1
        Else
            .Range("C4").Value = "Fail"
        End If
        
        If Len(.Range("D3")) = 0 Then
            .Range("D4").Value = vbNullString
        ElseIf .Range("D3").Value < 6# Then
            .Range("D4").Value = "Pass"
            n = n + 1
        Else
            .Range("D4").Value = "Fail"
        End If
        
        If n = 3 Then
            .Range("E4").Value = "Pass"
        Else
            .Range("E4").Value = "Fail"
        End If
    End With
End Sub

[NWE]

I did not know you could use a Len() for that. Why are we declaring a "n" variant?

[Paul_Hossler]

I did not know you could use a Len() for that. Why are we declaring a "n" variant?

Len() = 0 is supposedly faster than testing for =""

"n" is Dim-ed as Long and just counts the number of times the B4, C4, and D4 = "Pass".

You could use B4=Pass And C4=Pass And D4=Pass, but if n=3 then all 3 are Pass

[NWE]

I see so DIM n as long is a short cut.

[Paul_Hossler]

I see so DIM n as long is a short cut.

Just a different way.

Since there are only 3 condititions to be tested, you could just AND then together like offthelp said in #2

[NWE]

But then if I had 40 of these, then N becomes useful..I get it.