Register
Log In
Unanswered
Home
February 11, 2015
Offline
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
Offline
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
Offline
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
Offline
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
Offline
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
Offline
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
Offline
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
Dashboards
June 25, 2016
Offline
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
New Member
August 25, 2016
Offline
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
