Increment/decrement a number in text cell
 

I have cells with text and numbers in XX#-XXX or XX#-XXX# and I would like to
increment or decrement just the number(s) in the cells selected with a macro.
Thank you for your help!

Increment/decrement a number in text cell
 

I forgot there is a third format as well XX#-XXXX I don't know if that makes
any diffrence

"PhilosophersSage" wrote:

I have cells with text and numbers in XX#-XXX or XX#-XXX# and I would like to
increment or decrement just the number(s) in the cells selected with a macro.
Thank you for your help!

Increment/decrement a number in text cell
 

Give this macro a try...

Sub IncrementNumbers()
Dim X As Long, Z As Long, Cell As Range, Numbers() As String
For Each Cell In Selection
Numbers = Split(Cell.Value, "-")
For X = 0 To 1
If Numbers(X) Like "*#" Then
For Z = Len(Numbers(X)) To 1 Step -1
If Mid(Numbers(X), Z, 1) Like "[!0-9]" Then
Numbers(X) = Left(Numbers(X), Z) & (1 + Mid(Numbers(X), Z + 1))
Exit For
End If
Next
End If
Next
Cell.Value = Numbers(0) & "-" & Numbers(1)
Next
End Sub

--
Rick (MVP - Excel)


"PhilosophersSage" wrote in
message ...
I forgot there is a third format as well XX#-XXXX I don't know if that
makes
any diffrence

"PhilosophersSage" wrote:

I have cells with text and numbers in XX#-XXX or XX#-XXX# and I would
like to
increment or decrement just the number(s) in the cells selected with a
macro.
Thank you for your help!

Increment/decrement a number in text cell
 

With values like:
12#-345 in your cells, select the cells and run:

Sub Inkrement()
For Each r In Selection
v = r.Value
s = Split(r, "#-")
s(0) = s(0) + 1
s(1) = s(1) + 1
r.Value = Join(s, "#-")
Next
End Sub

--
Gary''s Student - gsnu200908


"PhilosophersSage" wrote:

I have cells with text and numbers in XX#-XXX or XX#-XXX# and I would like to
increment or decrement just the number(s) in the cells selected with a macro.
Thank you for your help!

Increment/decrement a number in text cell
 

Hmm, in my response, I assumed the X's were text (non-digits) and the #
signs were the numbers.

--
Rick (MVP - Excel)


"Gary''s Student" wrote in message
...
With values like:
12#-345 in your cells, select the cells and run:

Sub Inkrement()
For Each r In Selection
v = r.Value
s = Split(r, "#-")
s(0) = s(0) + 1
s(1) = s(1) + 1
r.Value = Join(s, "#-")
Next
End Sub

--
Gary''s Student - gsnu200908


"PhilosophersSage" wrote:

I have cells with text and numbers in XX#-XXX or XX#-XXX# and I would
like to
increment or decrement just the number(s) in the cells selected with a
macro.
Thank you for your help!

Increment/decrement a number in text cell
 

I tried it no joy... gives me a type mismatch. It may be because the values
I am working with are like:
TB#-1
TB#-05F#
PR#-WHT
TR#-SHLD
All items have two leading letters and then a number, and it is only the (#)
number that I want to increment or decrement. Then a few have a trailing
number that also need to be changed as well.

"Gary''s Student" wrote:

With values like:
12#-345 in your cells, select the cells and run:

Sub Inkrement()
For Each r In Selection
v = r.Value
s = Split(r, "#-")
s(0) = s(0) + 1
s(1) = s(1) + 1
r.Value = Join(s, "#-")
Next
End Sub

--
Gary''s Student - gsnu200908


"PhilosophersSage" wrote:

I have cells with text and numbers in XX#-XXX or XX#-XXX# and I would like to
increment or decrement just the number(s) in the cells selected with a macro.
Thank you for your help!

Increment/decrement a number in text cell
 

Did you try the code I posted elsewhere in this thread? It works for all of
these examples that you just posted.

--
Rick (MVP - Excel)


"PhilosophersSage" wrote in
message ...
I tried it no joy... gives me a type mismatch. It may be because the
values
I am working with are like:
TB#-1
TB#-05F#
PR#-WHT
TR#-SHLD
All items have two leading letters and then a number, and it is only the
(#)
number that I want to increment or decrement. Then a few have a trailing
number that also need to be changed as well.

"Gary''s Student" wrote:

With values like:
12#-345 in your cells, select the cells and run:

Sub Inkrement()
For Each r In Selection
v = r.Value
s = Split(r, "#-")
s(0) = s(0) + 1
s(1) = s(1) + 1
r.Value = Join(s, "#-")
Next
End Sub

--
Gary''s Student - gsnu200908


"PhilosophersSage" wrote:

I have cells with text and numbers in XX#-XXX or XX#-XXX# and I would
like to
increment or decrement just the number(s) in the cells selected with a
macro.
Thank you for your help!