Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 20

Thread: forumulas in macros?

  1. #1

    Join Date
    Apr 2002
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  2. #2
    Board Regular
    Join Date
    Apr 2002
    Posts
    115
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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....

  3. #3

    Join Date
    Apr 2002
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    how?

  4. #4
    Board Regular
    Join Date
    Apr 2002
    Posts
    115
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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?

  5. #5
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    If A1 has your number and you want B1 to equal A1 + 1 use the following code
    Code:
    [b1] = [a1] + 1

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,184
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.


    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

  7. #7

    Join Date
    Apr 2002
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  8. #8
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  9. #9

    Join Date
    Apr 2002
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  10. #10
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,184
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!

    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •