Increase Cell Number by 1

A

adamsm

Active Member
Joined
Apr 20, 2010
Messages
444
Hi anyone,

How could I wrote a code that would increase the cell "M10" value by number one each time the macro in run.

My sheets name is "Me". The cell format is in 0007/11

I want the code to change the number to 0008/11 when the macro is run.

Any help on this would be kindly appreciated.
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Sort by date Sort by votes
Code: 
Sub addNum()
Dim t
t = Split(Range("M10").Text, "/")
t(0) = Format(t(0) + 1, "0000")
Range("M10") = Join(t, "/")
End Sub
 
Upvote 0
You would put the worksheet name as the argument to the Worksheets property and that would be placed in front of the two Range calls...

Code: 
Sub AddNum()
    Dim t
    t = Split(Worksheets("Me").Range("M10").Text, "/")
    t(0) = Format(t(0) + 1, "0000")
    Worksheets("Me").Range("M10") = Join(t, "/")
End Sub

In case you are interested, here is another way to do the same thing...

Code: 
Sub AddOne()
    Dim S As String, Digits As Long
    S = Worksheets("Me").Range("M10").Value
    Digits = InStr(S, "/") - 1
    Mid(S, 1, Digits) = Format(Mid(S, 1, Digits) + 1, String(Digits, "0"))
    Worksheets("Me").Range("M10").Value = S
End Sub
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

Y
  • Solved
VBA to increase numbers by 1
Replies
8
Views
115
Fluff
Fluff
K
  • Solved
Automatic sequential number when printing
Replies
5
Views
442
mumps
mumps
G
  • Solved
Textbox Value +1
Replies
4
Views
102
gilly01625
G
D
  • Question Question
Dashboard Create a button to increase Monthly count
Replies
1
Views
185
sxhall
S
E
  • Question Question
Negative times and conditional formating.
Replies
2
Views
263
Ludwig
L

Forum statistics

Threads
1,224,525
Messages
6,179,317
Members
452,905
Latest member
deadwings

Share this page

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
Back
Top