Vba Function for a counter

  • Hi there,

    I need to keep memorized a progressive number in a function.

    I have a file with some invoices to be repeated in a new sheet, the invoices have a progressive number. This number is relevant to avoid double registration in the new sheet. How can I keep this number in a function and start from it for updating.

    At the moment I am using a cell in a worksheet (but I 'd like to use a function)

    protocol_number = activecell.value (I get the prot nr in a sub)

    different sub
    if Range("a1000") = activecell.value then

    Msgbox "the protocol number has been registered yet"
    update code

    Thanks for helping me

  • Re: Vba Function for a counter

    hello rafdepet welcome to the forum...

    please use code tag's when ever posting code it will help the person to see the code more easier way its a # sign on the right hand corner of the quick reply..

    for your question you cannot store the number in function from where you can retrieve it as after your macro finishes the code it deletes all the variables stored into it, you can check the last invoice number where you store the data eg:- if you have the invoice number into a sheet in column "A" then use

    lst_number = range("A65536").end(xlup).value

    so no need of storing it in another cell or you can use a find method to see if the invoice number entered is already present eg:-

    hope this helps...

  • Re: Vba Function for a counter

    Thank you Ashu,

    I think I was not so clear. I'm sorry about that. I'll try to clarify it.

    2 workbooks
    First one is a summary of a huge detailed (2nd) workbook for a big Financial Company, with several invoices entered by an employee.
    Invoice numbers are different and alphanumeric (we are in Italy and it's possible). But it is mandatory in Italian accounting to set all the invoice entering by a progressive number called protocol number.
    This protocol number marks the line of input with invoice number, amount, company, vat, and date of issue.

    Transferring the data to the summary workbook I always let the code start from the beginnig (as the pick up of invoices is always retrieved from a different Financial operating software, which creates a new excel file with all invoices, even the ones already reported on the summary workbook), keep looking to the protocol nr, match with the last one entered and from the matched one keep updating the summary workbook. (remember the protocol number in the only data not transferred in the summary workbook, if it was I could match it)
    Now I used to put the last protocol number used in a cell which can be cancelled or overwritten or other. So I was asking if there is a function (and you clerly said is not possible) that keeps the last protocol number entered and on the next updating match it with the invoice workbook and go further with updating if needed.
    I do not post the code as I am still using the cell as protocol number keeper.

    Hope this is clear

  • Re: Vba Function for a counter

    can't you match the details with a unique concanate of invoice number and amount and date with the main summary workbook from where you have to fetch the data and paste it into your workbook.

  • Re: Vba Function for a counter

    Thanks Ashu,

    yes I can. But as I explained invoice nr date and amount can be similar to another company (protocol nr can't, but the protocol nr. is the only data not transferred).
    I should match: company name, invoice nr, amount, and date for at least 35.000 input. This is why in Italy we have to use the protocol nr. because we can have almost the same name of a company (can even change a dot and it is another company) and of course same inv. nr, same date, same amount.
    Thanks anyway, you have been very kind

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!