forumulas in macros?

[bartlett]

is there any way in my macro to add one to a number. What i want to do is create my own invoice numbers. So when I get done with an invoice I can start a new invoice with a new invoice number automatically.

[bergy]

Have you considered using a workbook as a log? Each column could be a field from the invoice, including the invoice number. When you created a new invoice, the macro would go down the invoice column and then find the last invoice number. I don't think that would be too difficult....

[bartlett]

how?

[bergy]

When you ask "how?", do you mean how do you have the macro find the next number? If so, you could have the macro start at the top of the invoice column and then do a end(xlDown) to find the end of the data in that column. The number in that cell would be the last invoice (assuming the page is sorted by invoice number) and your next invoice would be that number + 1. Do you have a worksheet as a log?

[NateO]

If A1 has your number and you want B1 to equal A1 + 1 use the following code

[b1] = [a1] + 1

[Jack in the UK]

Im a finance guy on sale ledger.. this is my ground and no one has really come up with a workable solution if the company dont run finance packsge thats doing this so excel to the rescue..
YES it can be done but its a build brick by brick task

do you have the invoice template.

do you have table of addresses to ie debtor address list so we can use this to produce the inmvoice.

do you have stock or servoice sheet so we can charge the customer.

these are the 3 MUST then we can move on or its pointless.

[bartlett]

I am using a basic worksheet. I should say we are doing invoices one at a time where we enter the info, print, hit a macro to log into another worksheet, then enter the info again on the invoice. Cumbersome, yes, but we dont do many this way. And since we dont do many we want to be able to press the macro button and have the new invoice number set so when we come back to it we don't have to look up any old invoices. I apologize for the confusion-suggestions? thanks

[Jay Petrulis]

Hi,

Assume that your data is logged in sheet2 and the existing invoice numbers are in column A.

When the invoice is loaded and the form is reset, would setting the invoice cell to
=MAX(Sheet2!A:A)+1
work for you?

If it is in VBA, you can do the same thing very easily.

Sub test()
Dim InvoiceNumber As Long

InvoiceNumber = WorksheetFunction.Max(Sheets("Sheet2").Range("A:A")) + 1
MsgBox InvoiceNumber
End Sub

Bye,
Jay

[bartlett]

just thought of something-is there a way to set up a macro or formula to generate a number? sequentially?
just a thought.

[NateO]

Not sure I'm following, but if you want a separate worksheet for every invoice

Sub Invoicer()    
n = [a1].Value + 1
ActiveSheet.Copy after:=ActiveSheet
ActiveSheet.Name = "Invoice_" & n
[a1] = n
End Sub

Where cell a1 houses your invoice number. The only reason for copying the active sheet is to grab the formatting you can clear the contents quickly via code, e.g.,

[a7].clearcontents