string values do not equal cells, then return msg

[Kevin O'Neill]

I have a cell with "A,AA,AAA" minus the quotes. On another sheet I have
4 cells, each labeled, "A", "AA", "AAA", "AAAA"

The "A,AA,AAA" will be pulled apart using a split command as as string,
and compared to the 4 cells on the other sheet, If any part of the now
split string does not match any of the values in the 4 cells, then
return a msg.

So if on the first cell I had "A,AA,AAB" , "AAB" does not match any of
the 4 cell values on the 2nd sheet and will return an error. If all of
the now split cell values having a matching cell on the 2nd sheet, then
do nothing.

Any suggestions?

 

[Dave Peterson]

One way:

Option Explicit
Sub testme()

Dim mySplit As Variant
Dim myStr As String
Dim iCtr As Long
Dim myRng As Range
Dim AMatchWasFound As Boolean

Set myRng = Worksheets("Sheet2").Range("a1:A4")

myStr = "a,aa,aaa"
mySplit = Split(myStr, ",")
AMatchWasFound = True
For iCtr = LBound(mySplit) To UBound(mySplit)
If Application.CountIf(myRng, mySplit(iCtr)) = 0 Then
'not found
AMatchWasFound = False
Exit For
Else
'found
End If
Next iCtr

If AMatchWasFound Then
'no message
Else
MsgBox "at least one wasn't a match"
End If

End Sub

 

[Kevin O'Neill]

Thanks. This is what we eventually came up with.

Dim k As Integer
For k = 0 To 5 'stud input loop
Dim mycell
mycell = Cells(60, 3 + k)

Dim valueme() As String
valueme = Split(mycell, ",")
Dim d1 As Double
d1 = 0

Dim avar As Boolean
Dim i As Integer, j As Integer
Dim top
top = (Sheet12.Range("B1000").End(xlUp).Row - [Bookmark].Row +
2) / 4 - 1

For i = 0 To UBound(valueme) 'input loop split
avar = False
valueme(i) = Trim(valueme(i))
For j = 0 To top 'load combination name loop
name = Sheet12.Range("B" & [Bookmark].Row + 2 + j *
4).Value
If valueme(i) = name Then avar = True
Next j
If avar = False Then
MsgBox "You have entered and undefined Load Combination on
the Input Sheet!"
Exit Sub
End If
Next i
Next k