Increment number by 1

[Joe Derr]

Hey all.. I need help.. I think I am on the right track but I confused
myself..

I have a textbox 8 that has a number that is formatted like this...

JA00000

I want to, if OptionButton1 is selected, to auto increase the value of
Textbox8 by one, saving the time of haveing to enter the number in
when Im entering a lot of data.. here is what I have so far...

If OptionButton1.Value = True Then
'gets current Textbox8 value, assigns it a variable
NumInc = TextBox8.Value
'takes the right 5 digits of the value of textbox8
Number = Right(NumInc, 5)
'makes a newnumber by taking the 5 digits, adding 1
NewNumber = Number + 1
'for my t/s only
MsgBox (NewNumber)
End If

Now this does gleen the numbers, except the zeros.. how do I take this
number and add it back into the format JA00000 ??

If I ran it and I was entering item # JA00085, the troubleshooting
MsgBox would tell me the value of NewNumber is 86... I need it to put
in TextBox8, JA00086 ... and eventally, it will be moving over to
100's etc.. so I can't set it to put in JA000 + NewNumber ... It cant
be hardcoded, it has to take up all the digits into account, but if
there is only 2 digits in the number, I need the numbers preceding it
to be zeros..

Any ideas ??

Thanks for your Help!!!

Joe Derr

 

[Melanie Breden]

Hi Joe,

Hey all.. I need help.. I think I am on the right track but I confused
myself..

I have a textbox 8 that has a number that is formatted like this...

JA00000

I want to, if OptionButton1 is selected, to auto increase the value of
Textbox8 by one, saving the time of haveing to enter the number in
when Im entering a lot of data.. here is what I have so far...

If OptionButton1.Value = True Then
'gets current Textbox8 value, assigns it a variable
NumInc = TextBox8.Value
'takes the right 5 digits of the value of textbox8
Number = Right(NumInc, 5)
'makes a newnumber by taking the 5 digits, adding 1
NewNumber = Number + 1
'for my t/s only
MsgBox (NewNumber)
End If

Now this does gleen the numbers, except the zeros.. how do I take this
number and add it back into the format JA00000 ??

If I ran it and I was entering item # JA00085, the troubleshooting
MsgBox would tell me the value of NewNumber is 86... I need it to put
in TextBox8, JA00086 ... and eventally, it will be moving over to
100's etc.. so I can't set it to put in JA000 + NewNumber ... It cant
be hardcoded, it has to take up all the digits into account, but if
there is only 2 digits in the number, I need the numbers preceding it
to be zeros..

try this:

Dim intNumber As Integer

With TextBox8
If OptionButton1.Value Then
intNumber = CInt(Replace(.Text, "JA", "")) + 1
.Text = "JA" & Format(intNumber, "00000")
End If
End With

--
Regards
Melanie Breden
- Microsoft MVP für Excel -

http://excel.codebooks.de (Das Excel-VBA Codebook)

 

[Guest]

This is a good example for learning to use "Format" in VBA. See the statement below

'-------------------------------
Dim NumInc As Strin
If OptionButton1.Value = True The
NumInc = Textbox8.Tex
NumInc = Left(NumInc, 2) & Format(Right(NumInc, Len(NumInc) - 2) + 1, "00000"
Textbox8.Text = NumIn
MsgBox (NumInc
End I
'-------------------------------

Regards
Edwin Ta
(e-mail address removed)
http://www.vonixx.co

----- Joe Derr wrote: ----

Hey all.. I need help.. I think I am on the right track but I confuse
myself.

I have a textbox 8 that has a number that is formatted like this..

JA0000

I want to, if OptionButton1 is selected, to auto increase the value o
Textbox8 by one, saving the time of haveing to enter the number i
when Im entering a lot of data.. here is what I have so far..

If OptionButton1.Value = True The
'gets current Textbox8 value, assigns it a variabl
NumInc = TextBox8.Valu
'takes the right 5 digits of the value of textbox8
Number = Right(NumInc, 5)
'makes a newnumber by taking the 5 digits, adding
NewNumber = Number +
'for my t/s only :
MsgBox (NewNumber)
End I

Now this does gleen the numbers, except the zeros.. how do I take thi
number and add it back into the format JA00000 ?

If I ran it and I was entering item # JA00085, the troubleshootin
MsgBox would tell me the value of NewNumber is 86... I need it to pu
in TextBox8, JA00086 ... and eventally, it will be moving over t
100's etc.. so I can't set it to put in JA000 + NewNumber ... It can
be hardcoded, it has to take up all the digits into account, but i
there is only 2 digits in the number, I need the numbers preceding i
to be zeros.

Any ideas ?

Thanks for your Help!!

Joe Der

 

[alainB]

Hi,

On your example, this would work:

MsgBox Format (NewNumber, "JA00000")

Alain

 

[Joe Derr]

This worked GREAT too.. both ways did the job. I am wanting to learn
how to write better code.. any ideas.. thanks a bunch for your help!!!

Joe

 

[Joe Derr]

Thanks, it worked great.. I will take your code and learn what it does,
and how it works, so I will know next time.

intNumber = CInt(Replace(.Text, "JA", "")) + 1
..Text = "JA" & Format(intNumber, "00000")

Is there any books that you suggest so I study to learn more indepth
excel?

Thanks again!