how to test if multiple cells equal respective values in vba?

[joshman108]

I would like to set up a logic that says something like:

Check to see if cells A1:W1 are equal to these strings in succession: ("string1", "string2", "string3"), where string1 corresponds to A1, string2 corresponds to B1, string3 corresponds to C1 and etc. If any of the cells to not equal their corresponding string value then abort and throw a message box saying something like "re order columns".

It would be nice if the message box said specifically which cell did not match it's correspond string value, for instance: "A1 does not say 'string1' ", but if that's not possible then that's fine. A general error will work.

I can of course do many different if statements checking the cells one by one but I would like a more elegant and easier solution.

Thanks!

 

[MrTeeny]

This might work for you

Sub CheckColumns()
Dim myarray As Variant, i As Long
myarray = Array("string1", "string2", "string3") ' add all extra 20 strings


For i = 1 To 3 ' change to 23 when you've populated all your array




If Cells(1, i).Value <> myarray(i - 1) Then MsgBox "Re-order columns, mismatch in column " & Replace(Cells(1, i).Address(True, False), "$1", ""): Exit Sub
Next i


End Sub

 

[joshman108]

This might work for you

Thanks! I will look into it later and let you know. It looks like it would work

 

[joshman108]

@MrTeeny that will work, thanks!