Increasing a cell value by 1

[Phil Floyd]

In cell D11 there is a number like 310012-245. The cell is formatted
General but it seems that it is seen as text when trying to advance the
number by 1 with code (to 310012-246). I have tried:
Range("D11").Value = Range("D11").Value + 1
but always get a debug error. What needs to be done to make this work?

Thanks,
Phil

 

[Bob Phillips]

Hi Phil,

A bit more complex, but it works

With Range("D11")
iPos = InStr(1, .Value, "-")
If iPos > 0 Then
.Value = Replace(.Value, "-", "")
End If
.Value = .Value + 1
If iPos > 0 Then
.Value = Left(.Value, iPos - 1) & "-" & Right(.Value,
Len(.Value) - iPos + 1)
End If
End With


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Steve Garman]

It woud help to know the rules for what patterns constitute your numbers

From the information you have given, my best guess is:

Sub test()
Dim pos%, temp$, num%
temp$ = Range("D11").Value
pos% = InStr(temp$, "-")
num% = Mid$(temp$, pos + 1)
temp$ = Left$(temp$, pos%) & num% + 1
Range("D11").Formula = temp$
End Sub

It may be significantly easier if the "-" is always at the same position

 

[Phil Floyd]

Thanks Bob. You were correct...it is a bit more complex.

Phil

 

[Phil Floyd]

Steve,
The numbers to the left of the - is a job number. It always remains the
same. The numbers to the right of the - is a Purchase Order number and that
number increases sequentially with each PO issued.

Phil

 

[NickHK]

Phil,
Would it be easier to split this "number" into its 2 parts, then you can
group, filter etc better.
Incrementing your PO number is then a sinch (spelling?).

NickHK