Checking a Range for a Value, If another cell has a specific value

[LordDragon]

Greetings & Salutations,


I need a little more help please.


If cell ("A3") has a value of "Yes" then I need to check a Range("A27:A31") to see if any of them have a value of "Yes". If any of them do then nothing needs to happen, but if none of them do then I need to call a message box (that part I have ready to go).


It doesn't matter which sheet I'm actually checking because I'll be applying it to a few sheets with similar checks (just the Ranges will change {to protect the innocent}).


I just can't figure out how to check a range for a specific value when it doesn't matter which cell in the range has the value.


I tried this, but keep getting an error.




'Warn the user if they have not selected a monitor
With ActiveWorkbook.Worksheets("Workstations")
If Range("A3").Value = strYes Then And Range("A7:A11").Value = strNo Then MonitorMessage
For Each cell In Range("A7:A11")
If cell = strYes Then
ElseIf cell = strNo Then
End If
End With



I have played with this concept and tried several different ways to express it, but nothing is working. :banghead:


I was looking at VLOOKUP, but haven't used that function much (actually, ever) so I'm not sure how to apply it. But I'm open to any method, so long as it works.


Thanks.

[wrightyrx7]

You could try this



With ActiveWorkbook.Worksheets("Workstations")
If Range("A3").Value = "Yes" Then
For Each cell In Range("A7:A11")
If cell = "Yes" Then Exit Sub
Next cell
Call monitormessage
End If
End With

[LordDragon]

That almost works. I was trying a code very similar but the way I had mine written and this one I had an extra Else and End If in there, but they both are doing the exact same thing.

This is what I ended up using based on your suggestion, wrightrx7.



'Warn the user if they have not selected a monitor
With ActiveWorkbook.Worksheets("Workstations")
If Range("A3").Value = strYes Then
For Each cell In Range("A7:A11")
If cell.Value = strYes Then Exit For
Next cell
Call MonitorMessage
End If
End With


This is not it's own separate sub, it is part of another sub that I use to preview the order before it is placed. I was considering making it a separate Sub or Function and then "calling" it if cell "A3" has a "Yes".

But either way, right now it does warn if there is Not a "Yes" in Range("A7:A11"), but it also warns if there IS a "Yes" in that range.

[wrightyrx7]

That almost works. I was trying a code very similar but the way I had mine written and this one I had an extra Else and End If in there, but they both are doing the exact same thing.

This is what I ended up using based on your suggestion, wrightrx7.



'Warn the user if they have not selected a monitor
With ActiveWorkbook.Worksheets("Workstations")
If Range("A3").Value = strYes Then
For Each cell In Range("A7:A11")
If cell.Value = strYes Then Exit Sub
Next cell
Call MonitorMessage
End If
End With


This is not it's own separate sub, it is part of another sub that I use to preview the order before it is placed. I was considering making it a separate Sub or Function and then "calling" it if cell "A3" has a "Yes".

But either way, right now it does warn if there is Not a "Yes" in Range("A7:A11"), but it also warns if there IS a "Yes" in that range.


Hi LordDragon,

I see what you mean, my code works as it had 'Exit sub' but you cannot use it because this code above is being used within another Sub.


Could try it without the LOOP and use the FIND function



Dim Rng As Range
With ActiveWorkbook.Worksheets("Workstations")
If Range("A3").Value = "Yes" Then
With Range("A7:A11")
Set Rng = .Find(What:="Yes", _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Rng Is Nothing Then Call MonitorMessage
End With
End If
End With

[Aussiebear]

I've looked at this thread a number of times, and for some reason I think the logic of the code requirements is wrong. Basicily at present you are saying if A3.value equals yes and any cell in the range ('A27:A31"). value equals Yes then do nothing. Since you are chasing a Yes value, aren't we better off writing code to perform an action if the values are not Yes?

[wrightyrx7]

I've looked at this thread a number of times, and for some reason I think the logic of the code requirements is wrong. Basicily at present you are saying if A3.value equals yes and any cell in the range ('A27:A31"). value equals Yes then do nothing. Since you are chasing a Yes value, aren't we better off writing code to perform an action if the values are not Yes?

Im trying to get my head around how this would work without having all the code to see what the above code is for..

Am i being thick? haha :doh:

[LordDragon]

The idea is this:
The user orders a computer (desktop type). This comes in a Kit which includes everything you need, except the monitor because they come in several size choices. These monitors are in A7:A11. Since they only need one (usually) but they may not need any (they might already have one and only need the computer).

So if they order a computer but don't order a Monitor, my message boxes just ask them if they're sure.

So if they DO order a monitor, then nothing more needs to happen.

If we need to reverse the code to say that if A3 is yes, then check the range for a no then that's fine, but there only needs to be one Yes in that range for no further action to be needed.

Here is the code that previews the order:


Sub PreviewOrder(control As IRibbonControl)
'Unhides the Order Summary sheet.
'Finds the parts and quantities needed and copies them to the Order Summary sheet.


Application.ScreenUpdating = False

'Declare Variables
Dim wkSheet As Worksheet
Dim lngRow As Long
Dim strYes As String
Dim strNo As String
'Dim intWSRng As Integer

strYes = "Yes"
strNo = "No"
'intWSRng = Range("A7:A11")

'Warn the user if they have not selected a monitor
With ActiveWorkbook.Worksheets("Workstations")
If Range("A3").Value = strYes Then
For Each cell In Range("A7:A11")
If cell.Value = strYes Then Exit For
Next cell
Call MonitorMessage
End If
End With


'Unhide the Order Summary sheet.
With ActiveWorkbook
.Worksheets("Order Summary").Visible = True
End With


'Clear the Order Summary sheet to make sure the order isn't accidentally duplicated.
Sheets("Order Summary").Range("A2:D2000").ClearContents


'Declare which sheets to ignore.
For Each wkSheet In Worksheets

If wkSheet.Name = "Instructions" Then GoTo NextSheet
If wkSheet.Name = "Project Info" Then GoTo NextSheet
If wkSheet.Name = "Order Summary" Then GoTo NextSheet
If wkSheet.Name = "RMS Order" Then GoTo NextSheet
If wkSheet.Name = "Master DataList" Then GoTo NextSheet
If wkSheet.Visible = False Then GoTo NextSheet

wkSheet.Select

'Determine how many rows there are for each sheet
lngRow = Range("A" & Rows.Count).End(xlUp).Row


'Declare what to do with each sheet not ignored.
For Each cell In Range("A2:A" & lngRow)
If cell = strYes Then
Range(Cells(cell.Row, "C"), Cells(cell.Row, "E")).Copy
Sheets("Order Summary").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
End If

Next cell


NextSheet:
Next wkSheet

Application.ScreenUpdating = True
Application.CutCopyMode = False
Sheets("Order Summary").Select


End Sub


Here is the code for my Message Box Functions:


Function MonitorMessage()
'Warns the user if they ordered a workstation but did not order a monitor


'Declare the variables
Dim Msg As String
Dim Title As String
Dim Config As Integer
Dim ExclBox As Integer

'Set the Message Box settings.
Msg = "You ordered a workstation, but did not order a monitor."
Msg = Msg & vbNewLine & vbNewLine
Msg = Msg & "Would you like to add a monitor to your order?"
Title = "Warning!"
Config = vbYesNo + vbExclamation + vbDefaultButton1
ExclBox = MsgBox(Msg, Config, Title)

'Change the monitor selection to Yes.
If ExclBox = vbYes Then MonitorYes

'Verify the user does not want a monitor.
If ExclBox = vbNo Then MonitorNo


End Function




Function MonitorYes()
'Changes a montior selection to Yes


'Declare the variables
Dim strYes As String


strYes = "Yes"

If ActiveWorkbook.Worksheets("Project Info").WorkstationsBox.Value = strYes Then
ActiveWorkbook.Worksheets("Workstations").Range("A11").Value = strYes
End If

If ActiveWorkbook.Worksheets("Project Info").EDRBox.Value = strYes Then
ActiveWorkbook.Worksheets("EDR").Range("A31").Value = strYes
End If


End Function




Function MonitorNo()
'Verifies with the user they do not want a monitor.


'Declare the variables
Dim Msg As String
Dim Title As String
Dim Config As Integer
Dim ExclBox As Integer

'Set the Message Box settings.
Msg = "Are you sure you do not want a monitor?"
Title = "Are You Sure?"
Config = vbYesNo + vbQuestion + vbDefaultButton2
ExclBox = MsgBox(Msg, Config, Title)

'Change the monitor selection to Yes.
If ExclBox = vbYes Then MonitorYes


End Function




I think there are problems with these too, because I was able to get the boxes to appear using the code from the Exists Sub that wrightyrx7 provided. But the buttons didn't do what they were supposed to. That a different problem though. I'm focusing on the first one right now.

Thanks.

[wrightyrx7]

Hello,

Had a look your code and done what I think it needs to do. Tweaked bits of unnecessary code.

Cant really test it without your workbook or producing lots or test data.

Anyway give it a go see if its doing what you need.



Option Explicit


Sub PreviewOrder()
Application.ScreenUpdating = False

'Declare Variables
Dim ws As Worksheet
Dim lRow As Long, strYes As String, strNo As String
Dim nSheets
Dim i As Range, Rng As Range

strYes = "Yes"
strNo = "No"

'Warn the user if they have not selected a monitor
With ActiveWorkbook.Worksheets("Workstations")
If Range("A3").Value = "Yes" Then
With Range("A7:A11")
Set Rng = .Find(What:="Yes", _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Rng Is Nothing Then Call MonitorMessage
End With
End If
End With

'Unhide the Order Summary sheet.
Sheets("Order Summary").Visible = True

'Clear the Order Summary sheet to make sure the order isn't accidentally duplicated.
Sheets("Order Summary").Range("A2:D2000").ClearContents

'Declare which sheets to ignore.
nSheets = Array("Instructions", "Project Info", "Order Summary", "RMS Order", "Master DataList")
For Each ws In ActiveWorkbook.Worksheets
If Not IsNumeric(Application.Match(ws.Name, nSheets, 0)) And ws.Visible = True Then
'Determine how many rows there are for each sheet
ws.Select
'Declare what to do with each sheet not ignored.
For Each i In ws.Range("A2:A" & (ws.Range("A" & Rows.Count).End(xlUp).Row))
If i = strYes Then
ws.Range(ws.Cells(i.Row, "C"), ws.Cells(i.Row, "E")).Copy
Sheets("Order Summary").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
End If
Next i
End If
Next ws

Application.ScreenUpdating = True
Application.CutCopyMode = False
Sheets("Order Summary").Select


End Sub
Sub MonitorMessage()
'Warns the user if they ordered a workstation but did not order a monitor
'Declare the variables
Dim ExclBox As Integer

'Set the Message Box settings.
ExclBox = MsgBox("You ordered a workstation, but did not order a monitor." & vbNewLine & vbNewLine & _
"Would you like to add a monitor to your order?", vbYesNo + vbExclamation + vbDefaultButton1, "Warning!")

'Change the monitor selection to Yes.
If ExclBox = vbYes Then
Call MonitorYes
'Verify the user does not want a monitor.
ElseIf ExclBox = vbNo Then
Call MonitorNo
End If

End Sub




Sub MonitorNo()
'Verifies with the user they do not want a monitor.


'Declare the variables
Dim ExclBox As Integer

'Set the Message Box settings.
ExclBox = MsgBox("Are you sure you do not want a monitor?", vbYesNo + vbQuestion + vbDefaultButton2, "Are You Sure?")

'Change the monitor selection to Yes.
If ExclBox = vbYes Then
Exit Sub
ElseIf ExclBox = vbNo Then
Call MonitorYes
End If


End Sub


Sub MonitorYes()
'Changes a montior selection to Yes
'Declare the variables
Dim strYes As String

strYes = "Yes"

If ActiveWorkbook.Worksheets("Project Info").WorkstationsBox.Value = strYes Then ActiveWorkbook.Worksheets("Workstations").Range("A11").Value = strYes


If ActiveWorkbook.Worksheets("Project Info").EDRBox.Value = strYes Then ActiveWorkbook.Worksheets("EDR").Range("A31").Value = strYes


End Sub

[LordDragon]

Thank you very much. You'll notice I didn't use exactly what you provided, but I did use a lot of it and I like how much cleaner it is.

However, I don't believe I changed the meaning of any of the code you provided.

I'm still having a problem though. It is still claiming that no monitor was ordered, even if one was.



Sub PreviewOrder(control As IRibbonControl)
'Unhides the Order Summary sheet.
'Finds the parts and quantities needed and copies them to the Order Summary sheet.


Application.ScreenUpdating = False

'Declare Variables
Dim wkSheet As Worksheet
Dim lngRow As Long
Dim strYes As String
Dim strNo As String
Dim wksRng As Range
Dim edrRng As Range
Dim nSheets
Dim wsInstance As Range

strYes = "Yes"
strNo = "No"

'Warn the user if they have not selected a monitor
With ActiveWorkbook.Worksheets("Workstations")
If Range("A3").Value = strYes Then
With Range("A7:A11")
Set wksRange = .Find(What:=strYes, After:=.Cells(.Cells.Count), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True)
If wksRng Is Nothing Then Call MonitorMessage
End With
End If
End With


'Unhide the Order Summary sheet.
Sheets("Order Summary").Visible = True


'Clear the Order Summary sheet to make sure the order isn't accidentally duplicated.
Sheets("Order Summary").Range("A2:D2000").ClearContents


'Declare which sheets to ignore.
nSheets = Array("Instructions", "Project Info", "Order Summary", "RMS Order", "Master DataList")
For Each wkSheet In ActiveWorkbook.Worksheets
If Not IsNumeric(Application.Match(wkSheet.Name, nSheets, 0)) And wkSheet.Visible = True Then
'Determine how many rows there are for each sheet.
wkSheet.Select
'Declare what to do with each sheet not ignored.
For Each wsInstance In wkSheet.Range("A2:A" & (wkSheet.Range("A" & Rows.Count).End(xlUp).Row))
If wsInstance = strYes Then
wkSheet.Range(wkSheet.Cells(wsInstance.Row, "C"), wkSheet.Cells(wsInstance.Row, "E")).Copy
Sheets("Order Summary").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
End If
Next wsInstance
End If
Next wkSheet

Application.ScreenUpdating = True
Application.CutCopyMode = False
Sheets("Order Summary").Select


End Sub


Here is my code for the Message Boxes too:
I made no changes to this one.


Function MonitorMessage()
'Warns the user if they ordered a workstation but did not order a monitor


'Declare the variables
Dim Msg As String
Dim Title As String
Dim Config As Integer
Dim ExclBox As Integer

'Set the Message Box settings.
Msg = "You ordered a workstation, but did not order a monitor."
Msg = Msg & vbNewLine & vbNewLine
Msg = Msg & "Would you like to add a monitor to your order?"
Title = "Warning!"
Config = vbYesNo + vbExclamation + vbDefaultButton1
ExclBox = MsgBox(Msg, Config, Title)

'Change the monitor selection to Yes.
If ExclBox = vbYes Then Call MonitorYes

'Verify the user does not want a monitor.
If ExclBox = vbNo Then Call MonitorNo


End Function


This one, I realized I was asking if they wanted a monitor, but not offering them to enter an amount. So I put the extra code in that will automatically change the number of monitors to the number of computers. I'm considering throwing an input box in there so they can declare which monitor they want and how many. I'll mull it over a little.



Function MonitorYes()
'Changes a montior selection to Yes


'Declare the variables
Dim strYes As String


strYes = "Yes"

If ActiveWorkbook.Worksheets("Project Info").WorkstationsBox.Value = strYes Then
ActiveWorkbook.Worksheets("Workstations").Range("A11").Value = strYes
ActiveWorkbook.Worksheets("Workstations").Range("E3").Copy Range("E11")
End If

If ActiveWorkbook.Worksheets("Project Info").EDRBox.Value = strYes Then
ActiveWorkbook.Worksheets("EDR").Range("A31").Value = strYes
ActiveWorkbook.Worksheets("EDR").Range("E3").Copy Range("E31")
End If


End Function


I was experiencing a loop if Yes was clicked, it would ask again. Then I realized the last line was looking to vbYes to be pressed.


Function MonitorNo()
'Verifies with the user they do not want a monitor.


'Declare the variables
Dim Msg As String
Dim Title As String
Dim Config As Integer
Dim ExclBox As Integer

'Set the Message Box settings.
Msg = "Are you sure you do not want a monitor?"
Title = "Are You Sure?"
Config = vbYesNo + vbQuestion + vbDefaultButton2
ExclBox = MsgBox(Msg, Config, Title)

'Change the monitor selection to Yes.
If ExclBox = vbNo Then MonitorYes


End Function



In case you hadn't noticed, I do not like single letter (or even double letter) variables. I believe a variable should tell you what it does by it's name. Just my personal preference.

I have attached the entire file for your reference. Maybe that will help.

[wrightyrx7]

Hi LordDragon,

I was trying to avoid using the 'GoTo' function but cannot figure out why the 'Find' function isnt working in your workbook because it works when i create some test data in a new workbook.

So you could either create a new module and use the 'Exit Sub' code i provided near the start of this thread or use this:


Dim i As Long
'Warn the user if they have not selected a monitor
With ActiveWorkbook.Worksheets("Workstations")
If Range("A3").Value = strYes Then
For i = 7 To 11
If Range("A" & i).Value = strYes Then GoTo MonitorYes
Next i
Call MonitorMessage
End If
MonitorYes:
End With

[LordDragon]

It's funny how sometimes the first thing you try can work if you just make the right changes. I used the first set of code you suggested and put it in its own little sub, then called that sub if the value of A3 was Yes.

It works perfectly. Thank you so much for the help.

[wrightyrx7]

No problem, glad you have got it sorted.

Give me a shout if you get stuck with anything else :)