CODEWITHSUNDEEP
vbaexcelruntime-error
Philip Connell
Philip Connell

Reputation: 651

Runtime Error 13 type mismatch Excel

I hope you can help I have the below piece of code and I am getting runtime error 13 on this line 

If Sheets("Input").Range("A11:C100").Value = "" Then

What I am trying to achieve is to not let a user save the Excel workbook without populating the cells in the range ("A11:C100") The code works fine if it is just ("A11") but if I increase the range I get the error.

The rest of my code is below any help would be greatly appreciated. 

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

'Step 1: Check to see if Cell A1 is blank
    If Sheets("Input").Range("A11:C100").Value = "" Then

'Step 2: Blank: cancel the Close and tell the user
        Cancel = True
        MsgBox "Cells A11 to C100 can not be blank"
'Step 3: Not Blank; Save and Close
    Else
        ActiveWorkbook.Close SaveChanges:=True
    End If
End Sub

Pic of sheet enter image description here

Upvotes: 0

Views: 105

Answers (1)

user3598756
user3598756

Reputation: 29421

Sheets("Input").Range("A11:C100").Value returns a Variant array with all cells content in the range, so you can't compare it to a string like ""

  • if you need to check if at least one cell of the range is populated then

    instead of:

    If Sheets("Input").Range("A11:C100").Value = "" Then

    use

    If WorksheetFunction.CountA(Sheets("Input").Range("A11:C100")) = 0 Then

  • if you need to check if all cells of the range are populated then

    instead of:

    If Sheets("Input").Range("A11:C100").Value = "" Then

    use

    If WorksheetFunction.CountA(Sheets("Input").Range("A11:C100")) < Sheets("Input").Range("A11:C100").Count Then

Upvotes: 1

Related Questions