Change array to include new cells

**nsorden** · 08-14-2009, 12:44 PM

I am having a ton of trouble with this. I am trying to add a single text row in between existing data. I have tried multiple loops to try to place an array into Column G, but I cannot figure it out. Is there some coding that will produce the sort of result below in column G?

Col E	Col F	Col G
		
a	1	a
a	1	a
a	1	a
b	0	SUMMARY
b	0	b
b	0	b
b	0	b
b	0	b
c	1	b
c	1	SUMMARY
c	1	c
c	1	c
d	0	c
e	1	c
e	1	SUMMARY
f	0	d
f	0	SUMMARY
		e
		e
		SUMMARY
		f
		f
		SUMMARY

**JBeaucaire** · 08-14-2009, 12:53 PM

You actually need this done IN column G? Or is column G your representation of what you want to happen in column E?

We want the row inserted when the value in column E changes?

Is the word "SUMMARY" supposed to be entered, or a SUM() formula for that section? Or "Summary" in column E and a SUM() formula in column F?

**MickG** · 08-14-2009, 01:03 PM

Hi, Try:-

Sub MG14Aug52
Dim Last As Integer, Del As Long
Dim Rng As Range, Dn As Range
Set Rng = Range(Range("c1"), Range("c" & Rows.Count).End(xlUp))
     For Del = Rng.Count To 2 Step -1
        With Range("c" & Del)
            If Not .Value = .Offset(-1).Value Then
                .Insert
                .Offset(-1).Value = "Summary"
            End If
        End With
Next Del
End Sub

Regards Mick

**nsorden** · 08-14-2009, 02:06 PM

You actually need this done IN column G? Or is column G your representation of what you want to happen in column E?

We want the row inserted when the value in column E changes?

Is the word "SUMMARY" supposed to be entered, or a SUM() formula for that section? Or "Summary" in column E and a SUM() formula in column F?

1)Column G in the post is what I would like to happen IN column G
2)"Summary" should be entered when value in Column E changes
3)"Summary" actually entered, not a summation.

**nsorden** · 08-14-2009, 02:08 PM

Why column C?

Sub MG14Aug52
Dim Last As Integer, Del As Long
Dim Rng As Range, Dn As Range
Set Rng = Range(Range("c1"), Range("c" & Rows.Count).End(xlUp))
For Del = Rng.Count To 2 Step -1
With Range("c" & Del)
If Not .Value = .Offset(-1).Value Then
.Insert
.Offset(-1).Value = "Summary"
End If
End With
Next Del
End Sub

**nsorden** · 08-14-2009, 02:28 PM

MickG's solution seems to work, but I might have simplified my example a little too much. Each of the letters in the example corresponds to a number, so instead of the code placing a summary in between repeating letters, it needs to be placed in between non-repeating numbers that correspond to the letters. The letters determine the change but should not be placed in between the summaries.
Something similar to this:

Summary
10974457
73031672
25302339
Summary
28546618
19546623
29346619
29546621
29547624
Summary

**MickG** · 08-15-2009, 06:10 AM

Hi, Try this:-

Sub MG15Aug51
Dim Last As Integer, Del As Long
Dim Rng As Range, Dn As Range
Set Rng = Range(Range("e1"), Range("e" & Rows.Count).End(xlUp))
'Remove line below if Word "Summary Not wanted at Bottom of Col "G"
         Range("G" & Rng.Rows.Count + 1) = "Summary"

     For Del = Rng.Count To 2 Step -1
        With Range("e" & Del)
            If Not .Value = .Offset(-1).Value Then
                .Offset(, 2).Insert
                .Offset(, 2).Value = "Summary"
            End If
        End With
Next Del
End Sub