View Full Version : Returning a Value
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
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
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
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
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
But then if I had 40 of these, then N becomes useful..I get it.
Powered by vBulletin® Version 4.2.5 Copyright © 2025 vBulletin Solutions Inc. All rights reserved.