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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.