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

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

Try this:-

MsgBox ("Incorrect Value in Cell " & ActiveCell.Address)

I'll think about your other question.

Regards,

OssieMac

"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

MsgBox "Incorrect Value in Cell " & ActiveCell.Address(0,0)

--
Regards,
Tom Ogilvy



"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