Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Label the Groups
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Label the Groups
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(xlCellTypeConstan ts) 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? Laura wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Label the Groups
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 "Laura" wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing the font for part of an axis label, not the whole label. | Charts and Charting in Excel | |||
Excel - Sorting groups in groups due to subtotaling | Excel Worksheet Functions | |||
How to rezize data label box in pie charts (label wraps to two lin | Charts and Charting in Excel | |||
COPY LABEL FORM FROM EXCEL TO A LABEL | New Users to Excel | |||
how do i view all groups under excel in google groups | Excel Discussion (Misc queries) |