Hi, I have working Cal code which moves a set of numbers
(formatted as text) (eg. 2-20 or 9-44) down one cell and also increases the last number in the set by one. So as an example if 2-20 was in cell B30 and the macro was run 2-21 would be written to cell B31. The issue is the 2-20 (text) in cell B30 stays in the cell after it has been moved down one cell but I would like to have it deleted.
If you could please take a look at the code and see what is needed to make the change. I can upload the spreadsheet if that helps. Please let me know.
Thank-you.
-
I’m using Cal Version: 6.4.7.2
-
OS: Linux
Sub MOVE10PLUSDOWN()
Dim rng As Range, tmp, i As Long
'THE NUMBER SET COLUMN
Set rng = Range("B23:B45")
tmp = rng.Cells(rng.Cells.Count).Value
For i = rng.Cells.Count To 2 Step -1
rng.Cells(i).Value = Increment(rng.Cells(i - 1).Value)
Next i
rng.Cells(1).Value = Increment(tmp)
End Sub
'If value has a dash, increment the second number
Function Increment(v)
Dim rv, arr
rv = v
arr = Split(v, "-")
If UBound(arr) = 1 Then rv = arr(0) & "-" & CLng(arr(1)) + 1
Increment = rv
End Function