Message box "OK"

[mira2020]

hi all,

how can i write a VBA code to check value in 2 column
for row 2 to check if any value in column A is Yes -> display" Quantity is wrong"
if any value in column B is Yes -> display " Price is wrong"
Message only with "OK " button. If user click OK, just stop the VBA and do nothing
only when both column A & B are No, then continue doing the rest of the code

thanks

 

[Scott Huish]

Sub test()
Dim msgstr1 As String, msgstr2 As String
If Application.WorksheetFunction.CountIf(Range("A:A"), "Yes") Then msgstr1 = "Quantity is wrong"
If Application.WorksheetFunction.CountIf(Range("B:B"), "Yes") Then msgstr2 = "Price is wrong"
If Len(msgstr1 & msgstr2) Then MsgBox msgstr1 & Chr(10) & msgstr2, vbOKOnly: Exit Sub
'Rest of Code here
End Sub

 

[nemmi69]

as an example

Sub abc()
Dim TmMatch As Long

TmMatch = WorksheetFunction.Match("yes", Range("A2"), 0)
If TmMatch > 0 Then
    MsgBox "Quantity is wrong in row " & TmMatch
Exit sub
End If
TmMatch = WorksheetFunction.Match("yes", Range("B2"), 0)
If TmMatch > 0 Then
    MsgBox "Price is wrong in row " & TmMatch
Exit sub
End If
End Sub

 

[MicCh]

Sub CheckQuantityPrice()
    Dim A As Boolean
    Dim B As Boolean
    A = Sheet1.Range("A2").Value = "Yes"
    B = Sheet1.Range("b2").Value = "Yes"
    If A = False And B = False Then
        'doing the rest of the code
    ElseIf A Then
        VBA.MsgBox "Quantity Is wrong", vbCritical, "Wrong"
    ElseIf B Then
        VBA.MsgBox "Price is wrong", vbCritical, "Wrong"
    End If
End Sub

 

[Scott Huish]

Both of those only check A2 and B2 and only check for 1 possibility or the other, not both

if any value in column A

 

[MicCh]

Fix it

Sub CheckQuantityPrice()
    Dim iRange As Range
    Dim A As Boolean
    Dim B As Boolean
    Set iRange = Sheet1.Range("A:A").Find("yes")
    A = iRange Is Nothing
    Set iRange = Sheet1.Range("B:B").Find("yes")
    B = iRange Is Nothing
    If A And B Then
        'doing the rest of the code
    ElseIf A = False Then
        VBA.MsgBox "Quantity Is wrong", vbCritical, "Wrong"
    ElseIf B = False Then
        VBA.MsgBox "Price is wrong", vbCritical, "Wrong"
    End If
End Sub

 

[nemmi69]

How about.

Sub abc()
Dim TmMatch As Long

TmMatch = WorksheetFunction.Match("yes", Range("A2"), 0)
TmMatch = WorksheetFunction.Match("yes", Range("B2"), 0)

If TmMatch > 0 And TmMatch > 0 Then
    MsgBox "Quantity is wrong in row "
    Exit sub
Else If TmMatch > 0 Then
    MsgBox "Quantity is wrong in row "
    Exit sub
Else If TmMatch > 0 Then
    MsgBox "Price is wrong in row "
    Exit sub
End If
End Sub