Insert 2 rows, copy the formula.VBA HELP Pls

Microsoft Office excel 2007 home & stude...
July 28, 2010 at 16:28:31
Specs: Windows XP
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 O2

Sample data:
Fruit Type Total
Orange O 1
Apple A 2
Apple A 1
Orange O 10
Cherry C 2

After 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 2

THANK YOU SO MUCH


See More: Insert 2 rows, copy the formula.VBA HELP Pls

Report •


#1
July 28, 2010 at 16:45:27
This is setting off my Homework Alarm, but whatever. Off to the Office forum this thread goes.

Report •

#2
July 28, 2010 at 18:42:34
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 file you must have some pretty exotic ones.

Regards


Report •

#3
July 29, 2010 at 06:15:10
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

Report •

Related Solutions

#4
July 29, 2010 at 07:30:45
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 there only one 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 data only in three columns, as shown or is there data in more columns

Regards


Report •

#5
July 29, 2010 at 07:48:26
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 -1

Then test each cell in Column A, row n
If equal to "Orange" then:
Select the entire row below it (row n+1)
Insert row x2

Get 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


Report •

#6
August 4, 2010 at 19:32:16
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

Report •

#7
August 5, 2010 at 05:57:40
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


Report •


Ask Question