How to program an auto incrementable number in a cell, at each ope

[bestofcomputer]

I have a very basic excel calc :
- many fixed texts.
- a table with some data to be filled in many columns.
- one more column with automatic calculations, according to other columns.
So, you see, that is not terrific.
But still, there is something I dont how to do it, could you help me :
- 1 cell is called : "BILL NUMBER = "
- the next cell is blank square, and we are used to fill it manually, by
remembering what was the previous Bill Number we have used previously, and
then we simply increment that number, manually, for each new Bill.
- our format of bill number is = "KHA001", "KHA002", etc...
So, now, is it possible to program that cell, to automatically generate a
bill number in the same format as we already use, but not randomly, and using
numbers following each others, incrementally, increasing of 1 unit, from
previous bill, to the new bill ?
How to do that ?
Auto Calculation ?
Special Function ?
Macro ?
VBA ?
External File ?
Data Base ?
Access OLE ?
What else ?
Thank you so much to help me simplify my daily life...

[Toppers]

Hi,

Assume A1 contains the bill number which is increased by 1 each time a bill
is produced

i.a range("A1")=range("A1")+1

If B1 contains "BILL NUMBER = " then in C1 put
=CONCATENATE("KHA",TEXT(A1,"0000")

HTH

If A1 contains 123, C1 will be KHA0123


HTH

"bestofcomputer" wrote:

> I have a very basic excel calc :
> - many fixed texts.
> - a table with some data to be filled in many columns.
> - one more column with automatic calculations, according to other columns.
> So, you see, that is not terrific.
> But still, there is something I dont how to do it, could you help me :
> - 1 cell is called : "BILL NUMBER = "
> - the next cell is blank square, and we are used to fill it manually, by
> remembering what was the previous Bill Number we have used previously, and
> then we simply increment that number, manually, for each new Bill.
> - our format of bill number is = "KHA001", "KHA002", etc...
> So, now, is it possible to program that cell, to automatically generate a
> bill number in the same format as we already use, but not randomly, and using
> numbers following each others, incrementally, increasing of 1 unit, from
> previous bill, to the new bill ?
> How to do that ?
> Auto Calculation ?
> Special Function ?
> Macro ?
> VBA ?
> External File ?
> Data Base ?
> Access OLE ?
> What else ?
> Thank you so much to help me simplify my daily life...
>
>

[Toppers]

Typo:

Should be =CONCATENATE("KHA",TEXT(A1,"0000"))

"Toppers" wrote:

> Hi,
>
> Assume A1 contains the bill number which is increased by 1 each time a bill
> is produced
>
> i.a range("A1")=range("A1")+1
>
> If B1 contains "BILL NUMBER = " then in C1 put
> =CONCATENATE("KHA",TEXT(A1,"0000")
>
> HTH
>
> If A1 contains 123, C1 will be KHA0123
>
>
> HTH
>
> "bestofcomputer" wrote:
>
> > I have a very basic excel calc :
> > - many fixed texts.
> > - a table with some data to be filled in many columns.
> > - one more column with automatic calculations, according to other columns.
> > So, you see, that is not terrific.
> > But still, there is something I dont how to do it, could you help me :
> > - 1 cell is called : "BILL NUMBER = "
> > - the next cell is blank square, and we are used to fill it manually, by
> > remembering what was the previous Bill Number we have used previously, and
> > then we simply increment that number, manually, for each new Bill.
> > - our format of bill number is = "KHA001", "KHA002", etc...
> > So, now, is it possible to program that cell, to automatically generate a
> > bill number in the same format as we already use, but not randomly, and using
> > numbers following each others, incrementally, increasing of 1 unit, from
> > previous bill, to the new bill ?
> > How to do that ?
> > Auto Calculation ?
> > Special Function ?
> > Macro ?
> > VBA ?
> > External File ?
> > Data Base ?
> > Access OLE ?
> > What else ?
> > Thank you so much to help me simplify my daily life...
> >
> >

[Rock]

So, now, is it possible to program that cell, to automatically generate a
bill number in the same format as we already use, but not randomly, and using
numbers following each others, incrementally, increasing of 1 unit, from
previous bill, to the new bill ?

I don't know how do you "generate" a new bill? By each of the times that the file is opened? Or by clicking the sheet?

If yes, I am sure that there shuold be easier way to do.

However, I do not recommend this way of generating a new bill because it is easy to make a mistake that some one may "open" or "click" without intention to make a new bill.

I think you can write a small VBA code in Sheet1 (called "NewBill"), and create a button to assign this macro. The second macro called "Delete1bill", and create a button to assign this macro. In column A of sheet2, you store the sequence number of the Bills that created.

The "NewBill" have 3 simple tasks:
1- Look at the final cell of column A in sheet2, add 1 unit, then write this value to the place of the new bill.
2- Delete all the "written" content of the previous bill to create a new blank bill.
3- Write the number of this new bill to the last cell of column A in sheet2

The "Delete1bill" have 1 simple tasks:
1- Delete the last cell in column A in sheet2.
This macro will hepl you to delete a bill that by mistake, the "NewBill" button is clicked.

These are simple macro so I hope you can write these yourself. If not, just tell so that we can help you.

[bestofcomputer]

I am using Excel 2000 SP3.
And I have never used VBA, or even Macros...
I am ashamed, and very sorry...
So, your programming suggestions were very good, thanks a lot, but I am
unable to program these...
So, could you give me some details about how to do that ?
I would like also to send you my calc, dear Rock, but could you give me your
email ?
Or, I can give you mine, so that you can contact me, and I will forward you
my calc, so that you can include the codes, and mail it back to me.
My email : contact at bestofcomputers . com
Thanks.

"Rock" wrote:

>
> bestofcomputer Wrote:
> > So, now, is it possible to program that cell, to automatically generate
> > a
> > bill number in the same format as we already use, but not randomly, and
> > using
> > numbers following each others, incrementally, increasing of 1 unit,
> > from
> > previous bill, to the new bill ?
> >
>
> I don't know how do you "generate" a new bill? By each of the times
> that the file is opened? Or by clicking the sheet?
>
> If yes, I am sure that there shuold be easier way to do.
>
> However, I do not recommend this way of generating a new bill because
> it is easy to make a mistake that some one may "open" or "click"
> without intention to make a new bill.
>
> I think you can write a small VBA code in Sheet1 (called "NewBill"),
> and create a button to assign this macro. The second macro called
> "Delete1bill", and create a button to assign this macro. In column A of
> sheet2, you store the sequence number of the Bills that created.
>
> The "NewBill" have 3 simple tasks:
> 1- Look at the final cell of column A in sheet2, add 1 unit, then write
> this value to the place of the new bill.
> 2- Delete all the "written" content of the previous bill to create a
> new blank bill.
> 3- Write the number of this new bill to the last cell of column A in
> sheet2
>
> The "Delete1bill" have 1 simple tasks:
> 1- Delete the last cell in column A in sheet2.
> This macro will hepl you to delete a bill that by mistake, the
> "NewBill" button is clicked.
>
> These are simple macro so I hope you can write these yourself. If not,
> just tell so that we can help you.
>
>
> --
> Rock
>
>
> ------------------------------------------------------------------------
> Rock's Profile: http://www.excelforum.com/member.php...o&userid=29723
> View this thread: http://www.excelforum.com/showthread...hreadid=498985
>
>

[Rock]

Hi,

I tried to send you my email by mesage of this forum, but it doesn't work.
The message is "not found" for the user name of "bestofcomputer". I also send an email to ,,,,,, I hope you got it!


However, the macros look like this:

Sub Newbill()

i = 0
'Look for the code of the last bill
Do
i = i + 1
BillCode = Worksheets("Sheet2").Range("a1:a65000").Cells(i)
Loop Until BillCode = ""
If i = 1 Then
LastBill = Worksheets("Sheet2").Range("a1:a65000").Cells(i)
Else
LastBill = Worksheets("Sheet2").Range("a1:a65000").Cells(i - 1)
End If
BillCode = LastBill + 1

'Assumed that the place for bill code is in cell C3
'Remember to format this cell to KHA 000. Go to "Format" to do this
'Or you can use CONCATENATE() function to do this
Worksheets("Sheet1").Range("c3") = BillCode

'Delete the content of previous bill. Assumed they are in B3, B5, B7
Worksheets("Sheet1").Range("b3").Value = Clear
Worksheets("Sheet1").Range("b5").Value = Clear
Worksheets("Sheet1").Range("b7").Value = Clear

Worksheets("Sheet2").Range("a1:a65000").Cells(i) = BillCode
Worksheets("Sheet1").Range("a1").Select

End Sub
Sub DeleteBill()

i = 0
'Look for the code of the last bill
Do
i = i + 1
BillCode = Worksheets("Sheet2").Range("a1:a65000").Cells(i)
Loop Until BillCode = ""

If i = 1 Then
Worksheets("Sheet2").Range("a1:a65000").Cells(i).Value = Clear
Else
Worksheets("Sheet2").Range("a1:a65000").Cells(i - 1).Value = Clear
End If

End Sub
Good luck
(Had been corrected 2 times)

[bestofcomputer]

Dear Microsoft Newsgroup Administrators and Moderators :

A friend "Rock" has replied to my post in the Excel Progamming Forum,
on the 1/8/2006 6:49 AM PST, and, unfortunately, he has written in his post
my full Professional Email ID !
After this, I know what will hapen :
I will receive dayly may spams, junks, trojans, spywares, and viruses, from
very dangereous hacker groups (those who target the MS Newsgroup Posts !) !
So, PLEASE :
Do remove this previous post as soon as possible, and take all necessary
action, so that my Email ID remains protected from those predators !
Thank you so much for doing that.
I forgive to my friend for having done such a thing, because he is very kind
and very helpfull to me.
Bye.
Frederic Laroche.

NB : never post an Email ID in a Forum. Or, if you have to, encrypt it,
please !

[Rock]

I am very sorry Frederic Laroche!

I removed it !

Again sorry