forumulas in macros?

bartlett

Board Regular
Joined
Apr 24, 2002
Messages
52
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.
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
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....
 
Upvote 0
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?
 
Upvote 0
If A1 has your number and you want B1 to equal A1 + 1 use the following code
Code:
[b1] = [a1] + 1
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
just thought of something-is there a way to set up a macro or formula to generate a number? sequentially?
just a thought.
 
Upvote 0
nearly there the trick would be the VBA the save of the wkbk as the invoice and have the inv (UNIQUE inv number)

REMEMBER LEGALLY ALL INVOICE *MUST* HAVE DIFFERENT NUMBER and not have batches missing else the law will be on you after audits.

and have this invoice number in teh files name...

the VAT man will ove you.

invoice for my tips are in the post!
 
Upvote 0

Forum statistics

Threads
1,213,483
Messages
6,113,919
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top