Hello,

I have a huge file to work on. Please help me with a VBA that does the following for the entire sheet (until the last row)1) Whenever you see "Orange" in the "Fruit" column, Insert 2 rows after "Orange"

2) Copy the formula from the "Orange" row to the inserted rows in 1

3) First inserted row: Change the title in the "Fruit" column to Orange1 instead of Orange and Type O to O1

3) Second inserted row: Change the title in the "Fruit" column to Orange2 instead of Orange and Type O to O2Sample data:

Fruit Type Total

Orange O 1

Apple A 2

Apple A 1

Orange O 10

Cherry C 2After I run the macro I should get

Fruit Type Total

Orange O 1

Orange1 O1 1

Orange2 O2 1

Apple A 2

Apple A 1

Orange O 10

Orange1 O1 1

Orange2 O2 1

Cherry C 2THANK YOU SO MUCH

This is setting off my Homework Alarm, but whatever. Off to the Office forum this thread goes.

Hi, This must be a homework project.

Surely no one would need to do this for real !

I suggest that you do this project yourself.

<If> you have a specific part that you cannot get to work, post the code you have used and then someone is likely to offer a suggestion on what to do.

Oh... and how many types of fruit do you have. For this to be an

huge fileyou must have some pretty exotic ones.Regards

No this is not for school. I just used simple data to demonstrate what I was trying to do. I just need help with the logic then I can take the code and modify it for my work project. Thank you

Hi, I always worry about posts that have simplified data.

The simplification often means that the solution offered does not work on the real data.

Change it to main confidentiality / protect proprietary information, but is there any need to simplify it.I have some questions:

1. Is thereonlyone text string that is to be acted upon.

In your sample data only "Orange" is acted on. "Apple" etc. are ignored.

2. If you re-run the macro, will you still add two rows after each instance of Orange

3. If you add extra rows after Orange on a second run, will the names be Orange1 and Orange2, or Orange3 and Orange4

and the same applies to the Type (Type3 etc.)

4. If you re-run the macro will rows be added after Orange1 or Orange2

5. Where is the formula you mention.

It would be best if you identified the columns - Intial text ('Fruit') in column A and so on.

Which column is the formula in.

6. What does the formula reference. If you copy a formula and place it in a different cell, will the cell references in the formula be correct. Does the formula have to be modified in any way.

7. The Totals column - what is the rule for handling the duplication of the Total on the two inserted rows:

In the first instance of 'Orange' the Total is 1 and both inserted rows have totals of 1

In the second instance of 'Orange' the total is 10 but both inserted rows show totals of 1

8. Is there dataonlyin three columns, as shown or is there data in more columnsRegards

Hi, As to the logic for this:

With ActiveSheet

Find the last row with data

e.g. use:

Set rngEnd = .Range("A" & CStr(Application.Rows.Count)).End(xlUp)Create a For Next loop to work from the last row with data to the first:

For n = rngEnd.Row To 2 Step -1Then test each cell in Column A, row n

If equal to "Orange" then:

Select the entire row below it (row n+1)

Insert row x2Get the text in Column A, Row n and add "1" to it

paste it to Column A, row n+1

Repeat for column B and similar for column C (Total)Get the text in Column A, Row n and add "2" to it

paste it to Column A, row n+2

Repeat for column B and similar for column C (Total)Regards

Thanks Humar. I was able to get the code to work for my project.

I can't start the code from scratch myself but I can modify it. Thank you very much. Have a wonderful day

JennyVBA

Hi, Glad to hear that you got it to work.

I guess you can now sit back and enjoy the 'fruit' of your labors.

Regards

Humar

Ask Your Question

Weekly Poll

How long do you think until flying cars or taxis are common?

Discuss in The Lounge

Poll History