code for number automation

sangeeta25

Board Regular
Joined
Jun 15, 2015
Messages
59
I wanted to ask if anyone knows a code for the following action i woud like to take:

I have column G which only contains numbers, from low to high, i.e. cell G12 contains the number 123, G11= 124, G10= 125, G9= 126...... G3= 132, now G1 has a title for that column and G2 is made to be a button to insert a new row, so what i would like to do is have a code so that when one clicks to insert a new row, right now, G3 becomes blank and you must manually enter the next number, in this case 133, but to have that number automatically be inserted in the cell, thus again when another row is inserted G3 will now read 134 and then again when another row is inserted G3 will now automatically read 135, thus always the number above the number in the previous cell.

Is there a code to do this?

I will much appreciate your help.

NOTE: i would like a code, attempting to do this through simple automation does not work for what i specifically am asking


Sangeeta :)
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,245
Office Version
  1. 365
Platform
  1. Windows
This should do it:

Code:
Range("G3").EntireRow.Insert Shift:=xlDown
Range("G3") = Range("G4") + 1
 

sangeeta25

Board Regular
Joined
Jun 15, 2015
Messages
59
Hi,

Thanks for the reply, however once i've put the code in , an error box appears and reads the following: "Compile error: invalid outside procedure" and highlight where it reads "x1Down" in the code you have provided me with

Sangeeta
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,245
Office Version
  1. 365
Platform
  1. Windows
It doesn't say x1down it says xldown where you have changed the l to a 1.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,518
Messages
5,596,630
Members
414,082
Latest member
sasmita

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