If ANY cell in range match condition, then do this

[McCITech]

Good day gentlemen,

I am a novice VBA user and I'm finding that the simple things are often the most difficult. That or I'm having a brain fart...

I am creating a template for my work. As the user fills in data, the template responds with worksheet_change events giving the user feedback.
In this specific case, a data set of 5 points are entered. The conditional formatting changes the color of the cells, green if good, red if bad. This all works. In addition to this, the template gives an average of the 5 points and this value is compared to a nominal value.

If the average value is above or below a predefined range of values, then a 3rd cell displays the average with **#** format.

The issue I have is that if ANY of the 5 data point AND/OR the average value is out or range, then the 3rd cell must append the ** to the average value in the 3rd cell....and it's not working. The code I have will append the ** if the actual average is out of range, but Not if the data points are out.

Dim WSTol As Worksheet
Set WSTol = Worksheets("5 Readings")


Dim B27 As String

B27 = Format(WSTol.Cells(27, "B").Value, "Standard")     'keep the 00.00 format

For Each C In WSTol.Range("B21:B25", "B27").Cells
    
        If C.Value < WSTol.Cells(4, "L") Or C.Value > WSTol.Cells(4, "M") And C.Value <> 0 Then
        WSTol.Cells(29, "B") = "**" & B27 & "**"
        
        Else: WSTol.Cells(29, "B") = B27      'all data points & the average are within range
        End If
        
    Next

This code is what I have now. It half works, but I need to figure out how to say IF ANY of the values in the target range is true, then append **. But it's only working if the final cell in the range "B27" matches the criteria.

Thank you for any assistance you can provide

 

[JLGWhiz]

WSTol.Cells(29, "B") = "**" &[COLOR=#FF0000] B27 [/COLOR]& "**"        
        Else: WSTol.Cells(29, "B") = [COLOR=#FF0000]B27[/COLOR]

You will need to define what the value in red font is. VBA does not recognize it either as a variable or as text in the current configuration. A brief test indicatied that the code is checking your values in column B against the values in column L and M, but your code needs work to make sure your cells, variables and values are properly used.

 

[McCITech]

B27 is defined as a String Variable. It's value is set to worksheets.cells("B27") and formatted to keep the 0's after the decimal.

You are correct in that Column B is the user entered data. Column L is the lower limit of acceptable values and Column M is the upper limit of acceptable values.

Cell "B27" is the average of all 5 user entered values. I assigned that average value to the variable B27 so that I can append ** to the value and return it to the worksheet in a different cell ("B29").

IF any of the user entered data and/or the average are above or below the limits, the value in cell "B29" needs to have ** added to it.

The issue is that the user entered data Could be out side the limits, but the average is Still inside the limits, and Cell "B29" needs to reflect that Something is outside the limits.

Right now, the ONLY value that appends the ** is the Average value in "B27". If that average is outside the limits, it works great. But if the average is Good, the code leaves off the ** Regardless of the user entered data being outside the limits.

	125
	122
	123
	124
	121
Average	123
Nominal	120
Result	**123**

<tbody>
</tbody>

In this example; The user entered data of 125 is above the limit, but the average of all 5 is inside the limits.


Average = Cell "B27"


Result = Cell "B29"


My current code will give a result With or Without the ** depending on the value of the average, but does not give a result with the ** if the User Data is out.

I hope this clears things up...?

 

[Marcelo Branco]

I changed the names of the variables and created a new variable (boolean)
Also observe the range in blue (your way --> Range("B21:B25","B27") is not correct)

Sub aTest()
    Dim WSTol As Worksheet, bFound As Boolean
    Dim strB27 As String, rCell As Range
    
    Set WSTol = Worksheets("5 Readings")
    strB27 = Format(WSTol.Cells(27, "B").Value, "Standard")     'keep the 00.00 format
    
    For Each rCell In WSTol.[COLOR=#0000ff]Range("B21:B25, B27")[/COLOR].Cells
        If rCell.Value < WSTol.Cells(4, "L") Or rCell.Value > WSTol.Cells(4, "M") And rCell.Value <> 0 Then
            'A value out of range was found
            bFound = True
            Exit For
        End If
    Next
    
    If bFound Then
        WSTol.Cells(29, "B") = "**" & strB27 & "**"
    Else
        WSTol.Cells(29, "B") = strB27
    End If
End Sub

Hope this helps

M.

 

[McCITech]

Awesome! Many thanks to you for your help.

This has worked exactly as I needed.

Never occurred to me to set the range loop with a pass/fail and then use that as the condition to set up the final return.

Thanks again for your help!

 

[Marcelo Branco]

You are welcome. Glad to help.

M.