PDA

View Full Version : Solved: Check contents of cells in header row



clhare
05-23-2008, 05:49 AM
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?

' 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

mdmackillop
05-23-2008, 06:53 AM
You can't use Cells(1, i).Value if i = 0

clhare
05-23-2008, 07:00 AM
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
05-23-2008, 01:32 PM
Hi Cheryl
Arrays start at 0, Rows/Colums start at 1. i+1 might be a way to compare values???

david000
05-23-2008, 06:53 PM
the msgbox could tell you which one too.

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

Tom527
05-23-2008, 08:51 PM
if you type Option Base 1 in the declaration section arrays start at 1 not 0. then your program will work.

clhare
05-27-2008, 08:39 AM
I still can't get it to work. Can someone attach a sample file that checks the header values?

mdmackillop
05-31-2008, 06:15 AM
Try this.

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