PDA

View Full Version : Returning a Value



NWE
01-31-2019, 03:08 PM
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
01-31-2019, 04:25 PM
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
01-31-2019, 04:37 PM
Thanks that got it!

Paul_Hossler
01-31-2019, 04:55 PM
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
02-01-2019, 09:48 AM
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
02-01-2019, 06:29 PM
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
02-05-2019, 02:31 PM
I did not know you could use a Len() for that. Why are we declaring a "n" variant?

Paul_Hossler
02-05-2019, 02:51 PM
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
02-06-2019, 10:01 AM
I see so DIM n as long is a short cut.

Paul_Hossler
02-06-2019, 10:25 AM
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
02-06-2019, 10:31 AM
But then if I had 40 of these, then N becomes useful..I get it.