Give this a whirl... It is expandable by copying just the one line and
chaning the range and value to be checked. It also selects the incorrect cell
and lets the user know what the value should be...
Sub FindCol()
with Sheets("Data")
.Select
If CheckHeading(.Range("A1"), "TC #" ) Then Exit Sub
If CheckHeading(.Range("F1"), "Dept #") Then Exit Sub 'Copy this line ***
end with
End Sub
Private Function CheckHeading(ByRef rng As Range, _
ByVal strHeading As String) As Boolean
If UCase(Trim(rng.Value)) <> UCase(strHeading) Then
rng.Select
MsgBox "This heading is wrong. It should be """ & strHeading &
""".", vbCritical
CheckHeading = True
Else
CheckHeading = False
End If
End Function
--
HTH...
Jim Thomlinson
"PJFry" wrote:
> I have a spreadsheet that users paste a report into and I want to make sure
> the data are in the correct columns. (Many of the reports provided to my
> group have a similar layout, making it easy to make a mistake if you are not
> paying close attention.)
>
> Here is what I have:
>
> Sub FindCol()
> Sheets("Data").Select
> Range("A1").Select
> If ActiveCell.Value = "TC #" Then
> Range("F1").Select
> If ActiveCell.Value = "Dept #" Then
> Exit Sub
>
> Else
>
> MsgBox ("Incorrect Value in Cell " & Range(ActiveCell.Row & ":" &
> ActiveCell.Column))
>
> End If
> End If
>
> End Sub
>
> Two issues:
> 1) The MsgBox is supposed to return the location of the incorrect cell, but
> I get a type mismatch error. What do I need to do to convert the range to a
> string?
>
> 2) Is there a better way to go about this? This works fine on two cells,
> but if I ever want to add more, it will become increasingly difficult.
>
> Thanks!
> PJ
|
Similar Threads
