Vba Function for a counter

rafdepet · Jan 29th 2015

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)
range("a1000")=protocol_number

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

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

Thanks for helping me

ashu1990 · Jan 29th 2015

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

Code

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

Code

dim ran as range


set ran = range("A:A").find(what:= yournumber ,matchcase:=false) ''<--- checking the value in range("A:A")


if not ran is nothing then  ''<--- if found then do your code work




end if

Display More

hope this helps...

rafdepet · Jan 29th 2015

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

ashu1990 · Jan 29th 2015

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.

rafdepet · Jan 29th 2015

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

Vba Function for a counter

Participate now!

Share