PDA

View Full Version : [SOLVED] VBA Excel clear cells causes error 400



Galdorf
07-08-2016, 09:34 AM
Private Sub Workbook_Open()
Application.WindowState = xlMaximized
End Sub

Sub NextInvoice()
Sheets("InvoiceB").Range("F5").Value = Sheets("InvoiceB").Range("F5").Value + 1
Sheets("InvoiceB").Range("C8:C14").ClearContents
Sheets("InvoiceB").Range("F8:F14").ClearContents
Sheets("InvoiceB").Range("A17:G24").ClearContents
Sheets("InvoiceB").Range("A29:F36").ClearContents
End Sub

Sub SaveInvWithNewName()
Dim NewFN As Variant
ActiveSheet.Copy
NewFN = "C:\invoices\Inv" & Range("F5").Value & ".xls"
ActiveWorkbook.SaveAs NewFN, FileFormat:=xlWorkbookNormal
ActiveWorkbook.Close
NextInvoice
End Sub

For some reason i get an error 400 i am sure it has to do with 2nd ClearContents on the invoice cells are not protected i checked.
Seems to error 400 on merged text cells not sure how to get around it?.

SamT
07-08-2016, 11:56 AM
F8:F14?
2nd ClearContents on the invoice
Try clearing F8 alone

VBA doesn't play at all well with merged cells. Usually you can Format a group of cells with Horizontal Alignment = "Center Across Selection." VBA likes that.

Galdorf
07-08-2016, 12:42 PM
F8:F14?
Try clearing F8 alone

VBA doesn't play at all well with merged cells. Usually you can Format a group of cells with Horizontal Alignment = "Center Across Selection." VBA likes that.

I kind of found an easier way name the merged cells then :


Range("jobamt1").MergeArea.ClearContents