Macro to insert next number in sequence

  • I have been searching the web & forums but cannot find the solution I need.

    I have a template that is used for a calibration. We need to generate an automatic number to be used on the certificate, when the calibration is logged and certificate generated by office staff.

    All the calibrations are logged on a ledger file but the existing certificate numbers are prefixed with the operator initials and in some cases suffixed with an A. They are not necessarily in number order either. If a calibration is amended it is logged a second time, using the existing certificate number with the A suffix.

    I had envisaged adding a separate sheet ("Cert_Numbers") to the ledger file Sieve_Ledger_Latest.xls, with 3 columns Number - Date - Identifier.

    When the calibration is logged, firstly we need to check whether there is already a number. If there is no number within named range "Certificate_Number" I would like to add code to generate the numerical part of the certificate number by taking the next number in sequence from the Number column and save it to named range "Certificate_Number" within my file.

    I would like to save the current date and identifier (from named range "caldata11") next to the number on the "Cert_Numbers" sheet.


    Then my existing code would log the calibration on the ledger.

    This is the code I have for logging the calibration.

    I know that the code is a bitt messy with selects and activates but I'm struggling with those too.

    I would be grateful for any advice with this as I leave my job in 2 weeks and would like to see this finished. Thank you in advance,.

  • I think this would be easier if you provided a sample workbook.

    Also, just as an aside, you really shouldn't use ThisWorkbook as a variable name, since it's an intrinsic object in a workbook's VBA project.

    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • Hi Rory, I had asked for this thread to be deleted as I am trying to muddle through, have made a start and already altered small things.

    Which workbook do you require a sample of? I could provide sample of the ledger file. The calibration template is over 2MB with lots of background data etc.

    Example ledger file attached, column order in CertNum sheet changed from original post.EXAMPLE_Sieve_Ledger_Latest.xls

    I note your point re ThisWorkbook., thank you.

  • I have progressed a little. I now have 2 macros that work. The first one is testing to see if a new cert number is needed.

    The second one gets the next cert number from the ledger file.

    They both need tweaking and finishing and I have to decide how to incorporate both of them with logsievecalibration as in my first post.

  • Hello Roy,

    I misread the instructions and thought it was attached, sorry.

    Hopefully it is now attached.

    I have been moving on with my code and it logs the calibration if there is an existing certificate number. I can also manage to insert the next certificate number but then getting this to log is a problem because I can't work out how to return to the already open ledger file and get an error when trying to assign the new certificate number to the array. The certificate number "caldata11" will be the new number in variable NextCertNo with a prefix and possibly a suffix too. Please excuse me if I am not using the correct terminology.

    Here is my code. There are a couple of comments for where I am having problems.

  • This thread may be considered to be dead, thank you.

    What does that mean? If you've solved it yourself it's good forum etiquette to share your solution in the hope it might help someone else.

  • Here is my solution.

    I didn't share it earlier as I thought no one was reading the thread and it would be buried by all the previous posts.

  • As I said in the thread above "The workbook I didn't supply is full of sensitive information that I would have to strip out, so I will have to leave it there for the moment."

    It would have taken days to strip out all the commercially sensitive data and reduce the workbook to a non-functioning state so I couldn't attach it.