Increase Cell Number by 1

[adamsm]

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.

 

[Scott Huish]

Sub addNum()
Dim t
t = Split(Range("M10").Text, "/")
t(0) = Format(t(0) + 1, "0000")
Range("M10") = Join(t, "/")
End Sub

 

[adamsm]

Where should I write the sheet Name?

 

[Rick Rothstein]

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...

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...

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