February 11, 2015

Good afternoon, I hope everyone has a wonderful day!

I am using Excel 2013, PC

I need a macro that will skip all cells that have a “1” and go down to the next row and not run the rest of the macro. If the cell contains any other number I need it to continue with the rest of the macro.

Below is what I have to go down to the next row if the cell contains a “1”. The whole macro combined will skip 1 but when it hits the second 1 in another row it runs the macro. So the macro is running on every other line basically.

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

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

Below is the full macro I have

Sub skidtags()

'

' skidtags Macro

'

' Keyboard Shortcut: Ctrl+t

'

Last = Cells(Rows.Count, "AM").End(xlUp).Row

For i = Last To 1 Step -1

If (Cells(i, "AM").Text) = "#VALUE!" Then

Cells(i, "A").EntireRow.Delete

End If

Next i

ActiveSheet.Cells(5, 39).Select

Do Until IsEmpty(ActiveCell)

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

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

ActiveCell.Offset(0, -6).Range("A1").Select

ActiveCell.FormulaR1C1 = "0"

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

ActiveCell.Offset(0, -7).Range("A1").Select

ActiveCell.FormulaR1C1 = "=RC[4]/RC[-4]"

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

Dim n

Dim V

ActiveCell.Select

V = ActiveCell.Value

n = 1

Do Until n = V

ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select

Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

ActiveCell.Offset(-1, 0).Rows("1:1").EntireRow.Select

Selection.Copy

ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

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

Application.CutCopyMode = False

ActiveCell.FormulaR1C1 = "=R[-1]C+1"

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

n = n + 1

Loop

ActiveCell.Offset(0, -4).Range("A1").Select

ActiveCell.FormulaR1C1 = "=RC[8]"

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

ActiveCell.Offset(0, -7).Range("A1").Select

ActiveCell.FormulaR1C1 = "=ROUNDDOWN(RC[3]/RC[-4],0)"

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

ActiveCell.Offset(0, -6).Range("A1").Select

ActiveCell.FormulaR1C1 = "=RC[-25]-(RC[-1]*RC[-5])-R[-1]C[2]*R[-1]C[5]"

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

Loop

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

End Sub

Any advice would be greatly appreciated.

Thanks so much

Amy

Dashboards

June 25, 2016

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

Dashboards

June 25, 2016

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

February 11, 2015

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

Dashboards

June 25, 2016

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

February 11, 2015

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

Dashboards

June 25, 2016

Hi Amy

I am getting a little confused.

- I am looking for a macro to delete out any rows with an error in column “
- 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.

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

New Member

August 25, 2016

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.

Most Users Ever Online: 57

Currently Online: Max Neumann

17 Guest(s)

Currently Browsing this Page:

1 Guest(s)

Top Posters:

SunnyKow: 651

Frans Visser: 210

David_Ng: 96

mey tithveasna: 71

A.Maurizio: 60

rathanak: 58

yhooithin05: 54

Anders Sehlstedt: 47

julian: 46

PaulFogel: 37

Newest Members:

Ajay Mukhi

Stephen Motte

Rodney Hoyle

michael pitre

Samantha Burleson

Krishnaa Kurugod

David McSwain

Shameem Nassiree

Alanna Henderson

Janel Oz

Forum Stats:

Groups: 2

Forums: 18

Topics: 935

Posts: 4405

Member Stats:

Guest Posters: 1

Members: 42350

Moderators: 1

Admins: 3

Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea

Moderators: Genevieve Tupas