Macro help for this dumb blonde PLEASE!

[Hetta SdP]

Hey everyone.

We have started a new company and I am in the process of creating all our documentation.
I have created all the forms but need help with our quote/invoice-form.

What I want to achieve:
* I want the Quote/Invoice number to automatically skip to the next one in line after saving.
* When saving I would like quote/invoice to save in both excel and pdf.
*When opening new quote all non permanent fields cleared for new entry.

Now I have NEVER worked with macros before and everyone says that is the only way I will get this done. I really need someone to help me please?? Feeling like a real dumb blonde with this :P

The quote/invoice number will have two letters, 3 number, two letters : RK 001 Q ( is it possible that when you get to RK 999 Q that that the next one will start at RK 001 R?)

I am attaching a screenshot of the cells in question for quote/invoice number. ( the big blue block is just to block out the company's info)

I am hoping someone can help me... as I really have no clue how to do this...if at all possible.

 

[Misca]

Welcome to the board!

What you're looking for sounds like a perfect little job for a simple macro but unfortunately I'd need quite a bit more info to be able to help you with your code. Also, I wouldn't recommend using any macros by someone you don't know unless you can at least understand the basics of the code you're using. I mean macros can be really dangerous unless you really know what they're doing.

Having said that this doesn't sound too complicated but to be able to help you out with this one, we'd need to know a lot more about your worksheet (name, ranges etc.) & the folders you'd want the files ( a file= single worksheet only or the whole workbook, formulas included or over written etc.) to be saved in (folder names / structure, file names etc.).

I'm sorry I can't really help you that much with this little info but I was able to write a little function that generates the Quote/Invoice number for you:

Function QuotationNo(CurrentNo As String) As String

Dim QNStart As String
Dim QN As Integer
Dim QNEnd As String
Dim EndCode As Integer


QNStart = Left(CurrentNo, 3)    '3 first characters
QNEnd = Right(CurrentNo, 1)     'The last character
QN = Val(Mid(CurrentNo, 4, 3))  '3 digit number in the middle

If QN = 999 Then
    QN = 0
    EndCode = Asc(QNEnd)    'Checks the end letter
    
    If EndCode = 90 Then    'If "Z" then
        QNEnd = "A" 'Starts from A again
    Else
        QNEnd = Chr(EndCode + 1)    'Next letter
    End If
    
End If

QuotationNo = QNStart & Format(QN + 1, "000 ") & QNEnd

End Function

The first three characters of the number remains the same. The three digit number if the middle loops from 1 to 999 and every time it starts from 1 the last letter changes (looping from A to Z).

 

[Dan_W]

The quote/invoice number will have two letters, 3 number, two letters : RK 001 Q ( is it possible that when you get to RK 999 Q that that the next one will start at RK 001 R?)

Hi - so you wrote that each invoice number will have two letters - three numbers - two letters - but the examples you gave each has only one letter at the end. It would be helpful if you could please confirm if it is meant to be one or two letters at the end. Thank you!

I would add that Misca's script is extremely helpful to me for a hobby project - that'll save me some time, thanks Misca!

 

[Logit]

Misca

She is a brand new Excel coder. How does she use your FUNCTION ?

 

[Misca]

Misca

She is a brand new Excel coder. How does she use your FUNCTION ?

Good point!

The functions needs to be saved in a module in the workbook and the workbook needs to be saved as a ".xlsm" (or basically any other but the standard ".xlsx") type. Once you have the function in the module you can start using it as a normal (Excel) function. You just need to give it the previous number so that it can calculate the next one. Also, I didn't add any validations or error checks so in case the previous code is in any other format it might not work as you'd expect. The function is expecting to have four digits before the 3 digit number followed by one or more digits (and the last digit should be a capital letter). If there's more than just one letter at the end, the code only takes the last one & puts a space between the 3 digit number and the last letter.

If you don't know how to add a module to your workbook etc. you might want to start by watching some YouTube tutorials. The code is totally useless unless you know how to add the Developer tab to your Excel and insert a module and copy the function code to the module.