Please Help Simplify My Macro (Validate Columns Names)

[James thunderbolt]

Hi,

Wondering if someone could help me simplify this code, it is a basic validation that checks if the values in the first 10 columns match what I expect them to, I have come up with a fairly long winded approach, but hoping I could simplify it in a way that would make it easier to expand if I needed to.

Ideally - the way I imagine it looking - is that the columns values might be an array of strings, and a loop is created to check if, from column 1 to 10 the values match the corresponding column array.

This is my code below;

Sub TestValidation(o)

If Cells(3, 1).Text = "Col 1" And Cells(3, 2).Text = "Col 2" And Cells(3, 3).Text = "Col 3" And Cells(3, 4).Text = "Col 4" And Cells(3, 5).Text = "Col 5" And Cells(3, 6).Text = "Col 6" And Cells(3, 7).Text = "Col 7" And Cells(3, 8).Text = "Col 8" And Cells(3, 9).Text = "Col 9" And Cells(3, 10).Text = "Col 10" Then
MsgBox ("Yes")
Else
MsgBox ("No")
End If

End Sub

Many Thanks
Jango

 

[vletm]

James thunderbolt
One possible ..

Sub TestValidation()
    With ActiveSheet
        COK = True
        For x = 1 To 10
            If .Cells(3, x) <> "Col " & x Then COK = False
        Next x
        msg = "No"
        If COK Then msg = "Yes"
        MsgBox msg
    End With
End Sub

 

[Belleke]

a loop is created to check if, from column 1 to 10 the values match the corresponding column array.

Do you mean something like this?

Sub belle()
Dim rng As Range: Set rng = Application.Range("A3:J3")
Dim cel As Range
For Each cel In rng.Cells
  If cel.Value Like "Col*" Then
MsgBox ("Yes")
Else
MsgBox ("No")
End If
Next cel
End Sub

 

[Marc L]

Hi !

TEBV rule, as a beginner can achieve with an easy formula :​

Sub Demo4Noob()
    MsgBox IIf([COUNTIF(A3:J3,"Col *")] = 10, "Yes", "No")
End Sub

But, here, smarter is a matrix formula (array) :​

Sub Demo1()
    MsgBox IIf([SUM(-(A3:J3<>"Col "&COLUMN(A3:J3)))], "No", "Yes")
End Sub

Do you like it ? So thanks to click on bottom right Like !
​