Numerical Order Code for Macro

cc11

New Member
Joined
Apr 6, 2021
Messages
48
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2011
  6. 2010
  7. 2007
Platform
  1. Windows
I am trying to find a code or fix what i have on the macro that will automatically make the certain column generate in numerical order when I press the new button. So basically I have a macro created but everytime i generate a new line(row) with the new button assigned to the macro in that specific column it keeps putting the number 0001 in and i am wanting it to go in ascending/numerical order (1,2,3,4,5, etc.) That probably isnt going to make sense, let me know if I need to explain better...

Here is the macro that I have created/recorded:
Sheets("PO (0)").Select
Sheets("PO (0)").Copy After:=Sheets(3)
Sheets("PO Log").Select
Rows("9:9").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A9").Select
ActiveCell.FormulaR1C1 = "=R[-7]C[1]"
Range("B8").Select
Selection.AutoFill Destination:=Range("B8:B9"), Type:=xlFillDefault
Range("B8:B9").Select
Range("C9").Select
ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-2],RC[-1])"
Range("D9").Select
ActiveCell.FormulaR1C1 = "='PO (1)'!R[-3]C[3]:R[-3]C[5]"
Range("E9").Select
ActiveCell.FormulaR1C1 = "='PO (1)'!R[2]C[3]:R[2]C[4]"
Range("G9").Select
ActiveCell.FormulaR1C1 = "='PO (1)'!R[8]C[-4]"
Range("H9").Select
ActiveCell.FormulaR1C1 = ""
Range("H9").Select
ActiveCell.FormulaR1C1 = "=SUM('PO (1)'!R[12]C[1]:R[39]C[1])"
Range("I9").Select
ActiveCell.FormulaR1C1 = "='PO (1)'!R[41]C"
Range("J9").Select
ActiveCell.FormulaR1C1 = "='PO (1)'!R[40]C[-1]"
Range("K9").Select
ActiveCell.FormulaR1C1 = "='PO (1)'!R[42]C[-2]:R[43]C[-2]"
Range("H21").Select
End Sub


The column/Row this is affected is B8 and B9. B8 is 0000 and B9 is 0001. Then when i press new the next row that is generated is 0001 and repeats each time.
Please someone help, I need to get this fixed ASAP!!!!

Thanks!
 

cc11

New Member
Joined
Apr 6, 2021
Messages
48
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2011
  6. 2010
  7. 2007
Platform
  1. Windows
also on the pic po log 1 front half where it says job number that is not populating and it should come from the pic po log where job number is.

also here is a better pic of the PO log where you can see column letters and row numbers.
Screenshot 2021-04-13 093944.jpg
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

cc11

New Member
Joined
Apr 6, 2021
Messages
48
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2011
  6. 2010
  7. 2007
Platform
  1. Windows
Screenshot 2021-04-13 094105.jpg



and here is a better pic of the Po log 1 bottom half so you can see the rows and cloumns as well.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,045
Office Version
  1. 365
Platform
  1. Windows
OK, so it looks like you are talking about the formulas in columns D and E.
Did you adjust these formulas like I mentioned at the bottom of post 10 (the one that you marked as the solution)?
Your original formulas were relative references, and not absolute references, so as you copy formulas down the page, they will no longer line up.
 

cc11

New Member
Joined
Apr 6, 2021
Messages
48
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2011
  6. 2010
  7. 2007
Platform
  1. Windows
OK, so it looks like you are talking about the formulas in columns D and E.
Did you adjust these formulas like I mentioned at the bottom of post 10 (the one that you marked as the solution)?
Your original formulas were relative references, and not absolute references, so as you copy formulas down the page, they will no longer line up.
I did not adjust these i was unsure on how I was supposed to refrence those.

and yes columns d, e, and k on the "PO Log" picture.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,045
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I did not adjust these i was unsure on how I was supposed to refrence those.
That is necessary in order to get the code to work properly!

It is simply a matter of turning on the Macro Recorder, and re-entering the formula using absolute references (putting "$" in front of the column and row references), and taking the code that was created and putting that over the formula you have now.

If you don't feel comfortable doing that, can you tell me the exact formulas in cells D9 and E9 on your original sheet?
 

cc11

New Member
Joined
Apr 6, 2021
Messages
48
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2011
  6. 2010
  7. 2007
Platform
  1. Windows
Yes if you dont mind... these were the exact formulas that i had in there.

=@'PO (1)'!G6:I6 for D9


=@'PO (1)'!H11:I11 for E9

=@'PO (1)'!I51:I52 for K9
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,045
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

=@'PO (1)'!G6:I6 for D9


=@'PO (1)'!H11:I11 for E9

=@'PO (1)'!I51:I52 for K9
What exactly are you trying to return in those three cells?
You have entered a whole range of numbers, which doesn't really make much sense.
Are you trying to do a SUM or a COUNT? If so, you need to use one of the SUM or COUNT functions.
 

cc11

New Member
Joined
Apr 6, 2021
Messages
48
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2011
  6. 2010
  7. 2007
Platform
  1. Windows
so for D9 it will simply be a name that is entered from the PO tab in cells G6,H6,I6 that are all merged together.

for E9 it is the same thing but it will be a number that is entered into cells H11 and I11 that are merged togther

and for K9 it is also the same thing and will be the number thats enetered into cells I51 and I52 that are mereged together
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,045
Office Version
  1. 365
Platform
  1. Windows
If I can give you some advice, get rid of merged cells and never use them again!
I am serious. They are one of the worst inventions in Excel are cause all sorts of issues VBA, sorting, and other things. Most experienced programmers avoid them like the plague!
For centering across rows, there is a much better way of gaining that visual effect without all the issues caused by merged cells. See here: Tom’s Tutorials For Excel: Using Center Across Selection Instead of Merging Cells – Tom Urtis

So after you get rid of the merged cells, the VBA code formulas for columns D and E should now look like:
VBA Code:
    Range("D" & lr).FormulaR1C1 = "='" & ws & "'!R6C7"
    Range("E" & lr).FormulaR1C1 = "='" & ws & "'!R11C8"
and for column K would be:
VBA Code:
    Range("K" & lr).FormulaR1C1 = "='" & ws & "'!R51C9"

So for column K, the formula is referencing cell I51. Column "I" is the 9th column, and it is row 51.
Hence the: R51C9

You will probably need to do the same thing for the other columns (C, G, H, I, and J).
 

cc11

New Member
Joined
Apr 6, 2021
Messages
48
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2011
  6. 2010
  7. 2007
Platform
  1. Windows
Joe4 I can't thank you enough but thank you this fixed everything and it is all working smoothly now!! And thank you for the advice for the merged cell's.
I am going to read Tom's article here shortly.

Thanks again for all your help!!
 

Watch MrExcel Video

Forum statistics

Threads
1,130,422
Messages
5,642,021
Members
417,250
Latest member
spr1nger

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top