Macro and Formulas for Invoice Number

[AndrewSomiah]

Hello Excel Experts,

Could anyone help me with a Marco that I am trying to write. Basically my challenge is to write a Marco that automatically generates an invoice using the next sequence of numbers I have. I also want a Marco to save the last version of the invoice while generating a new one with the next number in sequence while clearing the data from the old worksheet.

I have managed to write the code thus far but when i run the marco which is assigned to a button I get a runtime error "13" type mismatch. I cannot spot the error in my code can anyone assis. Below is what i am trying to get working in my .xlsm file.

The debug is flaggin the error I have highlighted in red. I am not sure what i am doing wrong?

Cell C7 is where my invoice number is stored. Cell range A18:D21 is my data which is calculating cost. I hope I have given enough infpormation to help debug this code.

Sub NextInvoice()
Range("C7").Value = Range("C7").Value + 1
Range("A18:D21").ClearContents
End Sub


Sub SaveInvWithNewName()
Dim NewFN As Variant
'Copy invoice to a new workbook
ActiveSheet.Copy
NewFN = "C:\Users\Somiah\Documents\Invoice\Inv" & Range("C7").Value & ".xlsx"
ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook
ActiveWorkbook.Close
NextInvoice
End Sub

 

[Frostbite]

I think if you add Activesheet. in front of Range, it will fix the first one. You also may want to add it in front of your next line of code.

Activesheet.Range("C7").Value = Activesheet.Range("C7").Value + 1
Activesheet.Range("A18:D21").ClearContents

Try using this for the actual name of the document, where you need to insert the actual name of the worksheet.

NewFN = "C:\Users\Somiah\Documents\Invoice\Inv" & Sheets("Your Worksheet Name").Range("C7").Value & ".xlsx"

See if that helps out.

 

[AndrewSomiah]

Thanks Frostbite, I tried your suggestion but still get the same error message. What am I missing here:

Sub NextInvoice()
ActiveSheet.Range("C7").Value = ActiveSheet.Range("C7").Value + 1
ActiveSheet.Range("A18:D21").ClearContents
End Sub


Sub SaveInvWithNewName()
Dim NewFN As Variant
'Copy invoice to a new workbook
ActiveSheet.Copy
NewFN = "C:\Users\Somiah\Documents\Invoice\Inv" & Sheets("Invoice").Range("C7").Value & ".xlsx"
ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook
ActiveWorkbook.Close
NextInvoice
End Sub

 

[Rick Rothstein]

I have managed to write the code thus far but when i run the marco which is assigned to a button I get a runtime error "13" type mismatch. I cannot spot the error in my code can anyone assis. Below is what i am trying to get working in my .xlsm file.

The debug is flaggin the error I have highlighted in red. I am not sure what i am doing wrong?

Cell C7 is where my invoice number is stored. Cell range A18:D21 is my data which is calculating cost. I hope I have given enough infpormation to help debug this code.

Sub NextInvoice()
Range("C7").Value = Range("C7").Value + 1
Range("A18:D21").ClearContents
End Sub

Sub SaveInvWithNewName()
Dim NewFN As Variant
'Copy invoice to a new workbook
ActiveSheet.Copy
NewFN = "C:\Users\Somiah\Documents\Invoice\Inv" & Range("C7").Value & ".xlsx"
ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook
ActiveWorkbook.Close
NextInvoice
End Sub

You flagged two lines of code in red... are both of them raising the "Type Mismatch" error?

Also, what exactly is in cell C7? If they differ, what is in the cell and what is in the Formula Bar (tell us both)?

 

[AndrewSomiah]

Hello Rick ,

Thanks in advance for your assitance. Cell C7 has my invoice number which is 3 letters and 4 numbers example "MTS0001" . Yes both lines come up with the "type mismacth" error.

 

[Rick Rothstein]

Hello Rick ,

Thanks in advance for your assitance. Cell C7 has my invoice number which is 3 letters and 4 numbers example "MTS0001" . Yes both lines come up with the "type mismacth" error.

There is your problem... the contents of C7 is not a number, it is text... you cannot add 1 to text. Give this a try for your NextInvoice macro, it should solve both of your problems...

Sub NextInvoice()
  Dim Number As String, Text As String, C7value As String
  C7value = [C7]
  Text = Left(C7value, [MIN(FIND({0,1,2,3,4,5,6,7,8,9},C7&"0123456789"))] - 1)
  Number = Mid(C7value, Len(Text) + 1)
  Range("C7").Value = Text & Format$(Number + 1, String(Len(Number), "0"))
  Range("A18:D21").ClearContents
End Sub

 

[AndrewSomiah]

Thanks a lot Rick this works fine now. Awesome stuff. I have learnt something new. So if my invoice number wasn't a text string the original formula will have worked?
Great! I really appreciate the assistance.