Hi Amy

Maybe you could explain what you are actually trying to achieve.

The only code I can make sense of is you are deleting rows with error in column AM.

After that you are checking every row in column AM to see if it contains a 1. If it is, then fill the cell in column AG in the same row with 0.

Sunny Kow

Hi Amy

From my understanding of your codes, you want to do the following based on what is in column AM :

1) If it contains an error then delete the entire column.

2) If it contains a number >1 then copy that row and insert it one row below

3) Modify formulas in numerous columns after inserting that row

For 3 above, you should have already done all the formulas in your table.

If you have done that, then there is no need to modify any formula after copying.

Example : The entire column AF should already have the formula AJ5/AB5 etc but yours have values instead.

Same goes for all columns that require a formula.

Possible you want to increment the value in column AH if you make a copy of any row, but I am not sure.

I have attached a file that will do steps 1 to 2 for you. If you have added the formulas then that is all you need.

Sunny Kow

Hi Sunny Kow,

Sorry, this is what I need the macro to do....

Also, let me note that my co-worker is helping me write this macro.  (thanks Chris)...

I am looking for a macro to delete out any rows with an error in column “AM”

Then I want it to place a “0” in the Partial Layer Column “AG”

Then in the “AF” column take the “AJ” / “AB”

Then I need the macro to copy a row and paste values on the line below.  And add +1 to from the cell above in column “AH”. I then need this part of the macro to loop until “AH” cell = “AM”.

Once it has copied the row down as many times as the integer in column “AM” has, I need the macro to make “AI” =  “AQ”

Then I need “AF” to  =ROUNDDOWN( “AI” / “AB”,0)

Then make column “AG” = “H” – (“AF” * “AB”) – “AI” up a row * “AL” up a row

Then I need this macro to move on to the next row and continue until it reaches an empty cell.

The problem I am having with this macro is I want it to skip to the next row if “AM” = 1

My macro only skips every other “1” it finds

I hope this helps.

Thanks so much

Amy

Good morning Amy!!

That is quite a long list.

Sometimes it is best to describe your objective instead of showing the steps you intend to get the result.

It is possible that there may be better method to do what you need. There is always more than one way to solve an issue.

I will try and see what could be done from your list above.

Sunny Kow

Good evening SunnyKow

Yes, this list is long! 🙂

Basically when we have a lot of newspapers, we can only put so many on one pallet.  We need the macro to look at how many are ordered, how many I can get on a pallet, then if I need to add a pallet, to calculate what goes on each pallet. (or how many pieces I can get into one box)

I appreciate any help with this.  Thanks so much.

Amy / Chris

Hi Amy

I am getting a little confused.

1. I am looking for a macro to delete out any rows with an error in column “
2. Then I want it to place a “0” in the Partial Layer Column “AG”
3. Then in the “AF” column take the “AJ” / “AB”
4. Then I need the macro to copy a row and paste values on the line below.  And add +1 to from the cell above in column “AH”. I then need this part of the macro to loop until “AH” cell = “AM”.
5. Once it has copied the row down as many times as the integer in column “AM” has, I need the macro to make “AI” =  “AQ”
6. Then I need “AF” to  =ROUNDDOWN( “AI” / “AB”,0)
7. Then make column “AG” = “H” – (“AF” * “AB”) – “AI” up a row * “AL” up a row
8. Then I need this macro to move on to the next row and continue until it reaches an empty cell.

Why put a 0 into the entire column AG in (2) and them again modify it in (7)?

Why don't you just put the formula into column AF directly in (3). Why again adjust the formula of AF in (6)?

From (4) you basically need to copy as many rows as the value you see in column AM i.e. if it is 2 then make another copy and increment the number in AH.

If you could supply you expected result (as many as you can) , that would be best.

Sunny Kow

Hi Amy

Give this a try and see if this is what you want.

I did not do step (7) as I don't understand the underlined portion.

Then make column “AG” = “H” – (“AF” * “AB”) – “AI” up a row * “AL” up a row

Sunny Kow

Nice bit of code, SunnyKow!

Amy - the fundamental flaw with your macro is that you have your "if" statement on one line, and you don't have an "end if".

This might look like two lines, but the "_" instructs the code to read it as one line:

    If (ActiveCell.Text = "1") Then _

     ActiveCell.Offset(1, 0).Range("A1").Select

So what that really says is:

      If (ActiveCell.Text = "1") Then ActiveCell.Offset(1, 0).Range("A1").Select

This says "if activecell = 1 then select the cell below". That is all. The code will continue to run the following lines regardless of the outcome of your "if" statement.

To ignore lines of code when activecell doesn't = 1 you need to sandwich the lines to be ignored between "if" and "end if":

    If (ActiveCell.Text = "1') Then

        ActiveCell.Offset(1, 0).Select

        'Do whatever else you want to do if activecell = 1 here

    End If

Ben.

Hi Ben

Thank you 

Ben

Hello, thank you so much.  Sorry I've been offline for awhile. 

Thanks again,

Amy