Solved: Check contents of cells in header row

[clhare]

I have a macro that resizes/reformats the columns in a spreadsheet, but I'd like to add a "check" to the process to make sure that the columns are where I think they are before I do that.

I've got the following test code, but get an error. What am I doing wrong?

[vba]' Verify that headers are in the expected columns
Dim aryVals
Dim i As Long
aryVals = Array("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M")
For i = 0 To 12
If LCase(Cells(1, i).Value) <> LCase(aryVals(i)) Then
MsgBox "Headers do not appear as expected."
Exit Sub
End If
Next i

[/vba]

[mdmackillop]

You can't use Cells(1, i).Value if i = 0

[clhare]

So for this type of array, the first value is not zero? I changed the code to "For i = 1 to 13". I don't get an error now, but I do get the message stating the the headers are not as expected. How to I match up the loop with the correct columns?

[mdmackillop]

Hi Cheryl
Arrays start at 0, Rows/Colums start at 1. i+1 might be a way to compare values???

[david000]

the msgbox could tell you which one too.
[vba]
Sub dude()
' Verify that headers are in the expected columns
Dim aryVals
Dim i As Long
aryVals = Array("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M")
For i = LBound(aryVals) To UBound(aryVals)
If Len(Cells(1, i + 1).Value) = 0 Then
MsgBox "Headers do not appear as expected in >" & _
" " & Cells(1, i + 1).Address
Exit Sub
End If
Next i

End Sub
[/vba]

[Tom527]

if you type Option Base 1 in the declaration section arrays start at 1 not 0. then your program will work.

[clhare]

I still can't get it to work. Can someone attach a sample file that checks the header values?

[mdmackillop]

Try this.
[VBA]
Sub Test()
' Verify that headers are in the expected columns
Dim aryVals
Dim i As Long
aryVals = Array("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M")
For i = 0 To 12
If LCase(Cells(1, i + 1).Value) <> LCase(aryVals(i)) Then
MsgBox "Headers do not appear as expected." & _
vbCr & UCase(Cells(1, i + 1).Value) & ":" & UCase(aryVals(i))
Exit Sub
End If
Next i
End Sub
[/VBA]