if, and, or material list error checking

[Martin Bennett]

Hi All
Hoping someone can offer some advice, both my colleague and myself are far from experts with VBA. We have an excel workbook that collates multiple material lists using VBA and consolidates a final version for stores this is all working fine. We are now trying to add some error checking into the workbook for example if column B contains part numbers 123 & 125 does it contain part number 127 or 131 if it does take no action, if it does not Flag up a message Part number 127 or 131 missing notify engineering.

We have tried a number of different methods but can't get this to work i.e. if, and, or. I'm sure there is a very simple method and we are overlooking something


[TABLE="class: outer_border, width: 80"]
<tbody>[TR]
[TD]Part No.[/TD]
[/TR]
[TR]
[TD]123[/TD]
[/TR]
[TR]
[TD]124
[/TD]
[/TR]
[TR]
[TD]125[/TD]
[/TR]
[TR]
[TD]126[/TD]
[/TR]
[TR]
[TD]127[/TD]
[/TR]
[TR]
[TD]128[/TD]
[/TR]
[TR]
[TD]129[/TD]
[/TR]
[TR]
[TD]130[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Thanks in advance

 

[Yongle]

Try..

Sub CheckPartNo()

Dim b1 As Boolean, b2 As Boolean, b3 As Boolean, myStr As String, Rng As Range
Set Rng = Range("B:B")
With Application.WorksheetFunction
    b1 = (.CountIf(Rng, 123) <> 0 And .CountIf(Rng, 125) <> 0) = True
    b2 = .CountIf(Rng, 127) = 0
    b3 = .CountIf(Rng, 131) = 0
End With
    
If b1 Then
    If b2 Then myStr = 127
    If b3 Then myStr = 131
    If b2 And b3 Then myStr = 127 & vbCr & 131
End If

If Len(myStr) > 0 Then MsgBox myStr, , "Missing"

End Sub

Message box appears if either or both 127 and 131 are missing
If only want to know if both are missing then remove

    If b2 Then myStr = 127
    If b3 Then myStr = 131

 

[Martin Bennett]

Hi Yongle
Worked a treat, thank you for your help