Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Increment number by 1
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Increment number by 1
Hi Joe,
Joe Derr wrote: 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) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Increment number by 1
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 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Increment number by 1
Hi,
On your example, this would work: MsgBox Format (NewNumber, "JA00000") Alain --- Message posted from http://www.ExcelForum.com/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Increment number by 1
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 *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Increment number by 1
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! *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Increment invoice number | Excel Worksheet Functions | |||
Auto Increment Number | Excel Discussion (Misc queries) | |||
increment version number | Excel Discussion (Misc queries) | |||
How do I Increment a particular woksheet number? | Excel Worksheet Functions | |||
Increment an Invoice number | Excel Worksheet Functions |