Label the Groups

[Laura]

I have a spreadsheet with each row identified with a group name.

GrpName
Grp A
Grp B
Grp C
Grp B
Grp A
Grp C
Grp B

I have successfully built a macro to sort the data by the groups and put 3
blank rows between each group. So after the macro runs, it looks like this:


GrpName
<3 blank rows>
Grp A
Grp A
<3 blank rows>
Grp B
Grp B
Grp B
<3 blank rows>
Grp C
Grp C

I'm creating client-ready reports, so I need to label each group with a
little descriptive sentence.

What I want to end up with is:

GrpName
<2 blank rows>
The people in Group A are very nice.
Grp A
Grp A
<2 blank rows>
The people in Group B are sometimes nice.
Grp B
Grp B
Grp B
<2 blank rows>
The people is Group C are never nice.
Grp C
Grp C

I've been trying to get something to work all afternoon with all sorts of
interesting results. Any ideas?

Thanks in advance!

 

[Dave Peterson]

How about something like:

Option Explicit
Sub testme()

Dim myRng As Range
Dim myArea As Range

With Worksheets("sheet1")
Set myRng = Nothing
On Error Resume Next
Set myRng = .Range("a:a").Cells.SpecialCells(xlCellTypeConstants)
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "No constants in column A"
Exit Sub
End If
End With

For Each myArea In myRng.Areas
If myArea.Row > 1 Then
myArea.Cells(1).Offset(-1, 0).Value _
= "The people in " & myArea.Cells(1).Value & " are very nice."
End If
Next myArea

End Sub

This assumes that the values in column A are constants--not formulas. Is that
ok?

 

[gocush]

You might try something like:

Dim MsgA As String
Dim MsgB As String
Dim MsgC As String
Dim oCell As Range

MsgA = "AAAA"
MsgB = "BBBB"
MsgC = "CCCC"

For Each oCell In Range("A1:A100") ''ADJUST RANGE
Select Case Right(oCell, 1)
Case Is = "A"
oCell.Offset(-1, 0) = MsgA
Case Is = "B"
oCell.Offset(-1, 0) = MsgB
Case Is = "C"
oCell.Offset(-1, 0) = MsgC

End Select
Next oCell