Macro to insert Blank row and add data

[mystic42]

Hi

I am very new to writing VBA's and am stupped on getting this macro right, I need to do the following:

- When the value in a cell in column C changes compared to the cell value above - a blank line needs to be inserted between the different values

- then I need to copy the values from the cells below the Blank Row Columns A B C J L into the blank cell as well

and add on the blank line "Balance" in Column D

Any help would be greatly appreciated.

Thanks

Andrea

 

[mystic42]

Clarification : I am currently using Excel 2003 but am upgrading to 2010 at the end of the month:

A

B

C

D

E F G H I

J

K

L

Header Row

Header Row

1

Header Row

C2

Apples

C2

2

X123

Box

Apples

Balance

5,000.00

20.12.13

C3

Apples

C3

Apples

3

X123

Box

Apples

PURCHASE

….

5,000.00

20.12.13

C4

Apples

C4

Apples

4

X124

Box

Apples

SALE

….

- 2,000.00

21.12.13

b

C5

Apples

C5

Apples

5

X125

Box

Apples

PURCHASE

….

400.00

21.12.13

e

C6

Apples

a

C6

Apples

R

6

X126

Box

Apples

PURCHASE

….

4.00

04.01.14

f

C7

Oranges

f

C7

Apples

e

7

X127

Box

Apples

SALE

….

- 500.00

05.01.14

o

C8

Oranges

t

C8

s

8

z22

Cart

Oranges

Balance

550.00

13.12.13

r

C9

Pears

e

C9

Oranges

u

9

z22

Cart

Oranges

PURCHASE

….

550.00

13.12.13

e

C10

Pears

r

C10

Oranges

l

10

y35

Box

Oranges

SALE

….

- 500.00

20.12.13

C11

Pears

C11

t

11

etc..

C12

Pears

C12

Pears

C13

Pears

C14

Pears

C15

Pears

<tbody>
</tbody>

Hope this explains better what I am trying to do:

Thanks for any help that can be provided.

Regards

Andrea

 

[mirabeau]

Hi

I am very new to writing VBA's and am stupped on getting this macro right, I need to do the following:

- When the value in a cell in column C changes compared to the cell value above - a blank line needs to be inserted between the different values

- then I need to copy the values from the cells below the Blank Row Columns A B C J L into the blank cell as well

and add on the blank line "Balance" in Column D

Any help would be greatly appreciated.

Thanks

Andrea

Hi Andrea,

Maybe try the following VBA code. It should do literally what you ask for in the above quote, but if it's really what you want ... who knows?. Your posts 2 and 3 don't clarify much at all for me.

Sub insert_blank_rows_etcr()

Dim r As Long, c As Long, a, u(), i As Long, k As Long
r = Cells.Find("*", , , , xlByRows, xlPrevious).Row
c = Cells.Find("*", , , , xlByColumns, xlPrevious).Column
ReDim u(1 To r, 1 To 1)
a = Cells(3).Resize(r + 1)
Cells(c + 1) = 1: Cells(c + 1).Resize(r).DataSeries

For i = 1 To r
    If a(i, 1) <> a(i + 1, 1) Then k = k + 1: u(k, 1) = i
Next i

Cells(r + 1, c + 1).Resize(k) = u
Cells(1).Resize(r + k, c + 1).Sort Cells(c + 1), Header:=xlNo
Cells(c + 1).Resize(r + k).ClearContents

For i = 1 To k
    Cells(u(i, 1) + i, 1).Resize(, 3) = Cells(u(i, 1) + i + 1, 1).Resize(, 3).Value
    If i < k Then Cells(u(i, 1) + i, 4) = "Balance"
    Cells(u(i, 1) + i, 10) = Cells(u(i, 1) + i + 1, 10)
    Cells(u(i, 1) + i, 12) = Cells(u(i, 1) + i + 1, 12)
Next i

End Sub

 

[mystic42]

Thanks Mirabeau

Will try out on Monday, thanks again

Andrea

 

[mystic42]

Hello mirabeau

I get a runtime error 1004 and when I select debug the following line in highlighted:

a = Cells(3).Resize(r + 1)


Andrea

 

[mystic42]

Correction - Works perfectly - thank you

Andrea