Runtime Error 13 type mismatch Excel

[Conell8383]

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 A11 to C100 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

 

[hiker95]

Conell8383,

Try changing the following line of code from this:

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

To this:

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

 

[bobsan42]

This will produce the same error I guess - trying to compare multiple values against a single one (hence no problem when only A11 is compared)
A way to achieve your task is to loop through all cells in the range and check each one.
Another way:

if Join(Application.Transpose(Sheets("Input").Range("A11:C100").Value), "") = "" then

However - this means that if even only one cell is not empty the evaluation result will be TRUE.

 

[Conell8383]

@Bobsan42 and Hhiker 95: Cheers for the help guys. I got there in the end. the code that worked for is below. Again thank you for the help.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'Step 1: Check to see if Cell A1 is blank
    If WorksheetFunction.CountA(Sheets("Input").Range("A11:C100")) < Sheets("Input").Range("A11:C100").Count 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

 

[hiker95]

@Bobsan42 and Hhiker 95: Cheers for the help guys. I got there in the end. the code that worked for is below. Again thank you for the help.

Conell8383,

You are welcome.

Glad that you were able to solve your own request.

And, come back anytime.