Loop through worksheet and insert row after a change in a cells value

**AnthonyWB** · 07-26-2011, 02:04 PM

Hello everyone. I have a problem that I have not been able to resolve. I have a series of spreadsheets. They all have the same column headers. In particlar column 8 contains account numbers. I would like to loop though each worksheet and after each new account number insert a specific number of rows, say 5 of them.

I have piece mealed some code that can be seen below. It sort of works but 1.) It fails to loop through each worksheet and 2.) Does not properly insert rows after each new account number.

Note I do not want to run this macro o the first three worksheets of the work book. Only starting with worksheet 4 and onwards.

Please Login or Register  to view this content.

**Mordred** · 07-26-2011, 02:45 PM

Hi AnthonyWB, I have revised your code a bit. Let me know how it goes.

Please Login or Register  to view this content.

**AnthonyWB** · 07-26-2011, 04:01 PM

Thanks,

I slightly modified the code. Please see the error I am recieving:

Please Login or Register  to view this content.

**Mordred** · 07-26-2011, 04:27 PM

Hi AnthonyWb, it seems to be throwing an error because when your code inserts formulas, the values are removed. Also, I can get this to work with one iteration but not a second because of deleting columns. Plus, I don't know what's supposed to be going on with the data you are trying to manipulate.

**AnthonyWB** · 07-26-2011, 04:31 PM

Perhaps what I was trying to do through code is simply not working.

How would you go abut the task?

For example in Column F I have account numbers

a
a
b
b
b
c
c
c

I would like to insert a row whenever the acount number changes

a
a

b
b
b

c
c
c

**Mordred** · 07-26-2011, 04:41 PM

Hi AnthonyWB, try the following code and let me know if it meets your requirements:

Please Login or Register  to view this content.

**AnthonyWB** · 07-26-2011, 04:50 PM

Again thank you. Please see the sample output below. It alsmost works but not quite:

72363I
72363I

115230I

74267I
74267I

111548I

111550I

115540I

130960I

64598I

111430I
111430I

111866I

112294I

112296I

113092I

113094I

113666I

113736I

113750I

113928I

121716I

114354I

114356I

115392I

115508I

115644I
115644I

116216I

130068I

130960I

131200I
131200I

113570I
113570I

120524I
120524I

120526I
120526I

121012I
121012I

128730I <------Problems start
128896I
128896I
130014I
130014I
130874I
130876I
139836I
139836I
120148I
120148I
74235I
74577I
74577I
74345I
111866I
120246I
120246I
74637I

**Mordred** · 07-26-2011, 04:54 PM

I just ran the code and didn't have a problem, it all turned out like:

72363I
72363I

115230I

74267I
74267I

111548I

111550I

130960I

64598I

111430I
111430I

111866I

112294I

112296I

113092I

113094I

113666I

113736I

113750I

113928I

121716I

114354I

114356I

115392I

115508I

115644I
115644I

116216I

130068I

130960I

131200I
131200I

113570I
113570I

120524I
120524I

120526I
120526I

121012I
121012I

128730I

128896I
128896I

130014I
130014I

130874I

130876I

139836I
139836I

120148I
120148I

74235I

74577I
74577I

74345I

111866I

120246I
120246I

74637I

**AnthonyWB** · 07-26-2011, 04:57 PM

wow interesting

And on one of the spreadsheets my data shows up as:

111096I
111096I
111426I
111426I
113590I
113590I
117094I
117094I
120070I
120070I

133048I
133048I

141076I
141076I

141090I
141090I

120752I
120752I

120754I

**Mordred** · 07-26-2011, 07:42 PM

I wonder if it is because I had the range wrong, try:

Please Login or Register  to view this content.

**Mordred** · 07-26-2011, 07:46 PM

I'm at home now on a different computer and the code worked like a charm. Eg:

11
11

12

13

14
14
14

15
15

16

17

18
18
18
18
18

19
19

20
20

21

22
22

23

24
24
24

25
25
25

26

27
27
27

28
28

29

30
30
30
30
30
30

31
31
31
31

32

33
33
33
33
33
33
33

333

34

35

36

37

38

39

40
40
40

**Mordred** · 07-26-2011, 07:48 PM

Give it one more try and if it still doesn't work for you then it may be best if you upload a mock file.

**AnthonyWB** · 07-27-2011, 11:18 AM

Perhaps the problem is that in some instances there MAY exist a blank row in between account numbers. For example

a
a
a
b
b

c
c

d
d
d
d
e
e
e

It's close but slightly off.

**AnthonyWB** · 07-27-2011, 01:51 PM

yeah I "F8" through the code. the problem is when the code encounters a black space.

Please Login or Register  to view this content.

**AnthonyWB** · 07-27-2011, 02:06 PM

here is the corrected version

Please Login or Register  to view this content.

Now i need to do this by looping through the spreadsheets. Any suggestions

Loop through worksheet and insert row after a change in a cells value

LinkBack

Thread Tools

Rate This Thread

Display

Loop through worksheet and insert row after a change in a cells value

Re: Loop through worksheet and insert row after a change in a cells value

Re: Loop through worksheet and insert row after a change in a cells value

Re: Loop through worksheet and insert row after a change in a cells value

Re: Loop through worksheet and insert row after a change in a cells value

Re: Loop through worksheet and insert row after a change in a cells value

Re: Loop through worksheet and insert row after a change in a cells value

Re: Loop through worksheet and insert row after a change in a cells value

Re: Loop through worksheet and insert row after a change in a cells value

Re: Loop through worksheet and insert row after a change in a cells value

Re: Loop through worksheet and insert row after a change in a cells value

Re: Loop through worksheet and insert row after a change in a cells value

Re: Loop through worksheet and insert row after a change in a cells value

Re: Loop through worksheet and insert row after a change in a cells value

Re: Loop through worksheet and insert row after a change in a cells value

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions