Code to ADD numbers

[mcfly]

Can numbers be added to an entry automatically? if I am entering DE can it add 2301 then the next time I add DE it will add 2302, in the same cell, or not. It will not always be DE, it could be BE or DJ and so on. So if it was DJ it would start over at 2301 then 2302?

I hope this is not to complicated or confusing.

Here is the code I am using to enter in my text and other stuff.

Private Sub OBCOLOR_Change()
OBCOLOR.RowSource = "Sheet2!a1:a12"
End Sub


Private Sub CancelButton_Click()
Unload Me
End Sub

Private Sub OKButton_Click()
' Make sure Sheet1 is active
Sheets("Sheet1").Activate

'make sure a color is selected
If OBCOLOR.Text = "" Then
MsgBox "Enter a Color."
Exit Sub
End If

' make sure there is a request date.
If Textdate.Text = "" Then
MsgBox "Enter a Date."
Exit Sub
End If

' Make sure a name is entered
If TextNAME.Text = "" Then
MsgBox "You must enter a Design #."
Exit Sub
End If

' Determine the next empty row
nextrow = _
Application.WorksheetFunction.CountA(Range("A:A")) + 1
' Transfer the name
Cells(nextrow, 2) = TextNAME.Text
'TRANSFER THE COLOR
Cells(nextrow, 4) = OBCOLOR.Text
'TRANSFER THE DATE
Cells(nextrow, 1) = Textdate.Text

' Transfer the Garment Type
If Optionadult Then Cells(nextrow, 3) = "Adult"
If Optionyouth Then Cells(nextrow, 3) = "Youth"
If Optiontotal Then Cells(nextrow, 3) = "Total"

' Clear the controls for the next entry
TextNAME.Text = ""
OBCOLOR.Text = ""
Textdate.Text = ""
Optionadult = False
Optionyouth = False
Optiontotal = False
TextNAME.SetFocus

End Sub

As you can see I'm adding the text and numbers into TEXTname as DE2301,DE2302, etc..
Thanks for any replies!!
This message was edited by mcfly on 2002-09-10 14:48
This message was edited by mcfly on 2002-09-10 15:37

 

[Juan Pablo González]

I have no idea what you're talking about here !! what is DE ? where are you entering the texts ? or numbers ? where do you want the result ?

All I can think of is

Range("A1").Value = Range("A1").Value + 1

to cover your subject.

 

[Ephraim Flintwinch]

If you want to re-set the number to 2301 every time there is a change in the letters supplied, use two cells (can be on a different sheet to store the letters and the number) and revise code to :-

' Transfer the name
If textName = [A1].Value Then
[A2] = [A2] + 1
Else
[A1] = textName.Text
[A2] = 2301
End If
Cells(nextrow, 2) = [A1] & [A2]


If you want to retain the number for a particular set of letters and continue with the next number if those letters are supplied again, then you would need to maintain a list of the letters and the numbers.
The code would then need to check the letters supplied with the list, and update the list in a similar way to the code above.

 

[mcfly]

I have to enter new designs for the 2003 nascar season, the DE stand for Dale Earnhardt, JG stand for Jeff Gordon.
When I get a new art request for the 2003 season I give the design a new number so Dale Earnhardt's 1st design of the year will be DE2301 then his next design will be DE2302.
The same goes for any other driver design we create. Jeff Gordon's new design would be JG2301 and then JG2302 ETC..

I was just wondering if there was a way to make the numbering automatic because after around 400 to 2000 designs I have to constantly search for numbers.

I was thinking that if I typed in DE in my textbox it could asign the numbers starting at 2301 and then every time I entered DE or whatever it would assign the next number

Right now I don't care which cell or cells it goes in because I'm just trying to figure out how to implicate this, and when I know I will put it to use. So any cells are fine.

Thanks for the help, sorry I wasn't clear on this alittle bit more, but it seem the more I go into detail nobody replies.

 

[Ephraim Flintwinch]

What don't you understand about the suggestion i made?

 

[mcfly]

On 2002-09-10 18:33, Ephraim Flintwinch wrote:
What don't you understand about the suggestion i made?

Ephraim,
I am working with your code now, and it works good, but whenever I type in a design #(DE)it gives DE2301(which is what I want)and then type in a different design # (JG) it diplays JG2301(GREAT), but when I go back to DE it gives DE2301 again. So i'm trying to work with what you said at the bottom of your post, but I don't quite know how to go about it.

Thanks!!

 

[Ephraim Flintwinch]

The following will create and maintain a list of the names/numbers in columns A & B of worksheet "Sheet2" :-

Dim f As Range
With Worksheets("Sheet2")
Set f = .[A:A].Find(What:=textName)
If f Is Nothing Then
Set f = .[A65536].End(xlUp)(2, 1)
f = textName
f(1, 2) = 2301
Else
f(1, 2) = f(1, 2) + 1
End If
End With
Cells(nextrow, 2) = f & f(1, 2)

At the start of a new year, or whenever you want to start a new number sequence, you need to manually re-set the numbers in column B of "Sheet2" to zero.