Runtime Error 13 type mismatch Excel

Question

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

Will you consider to loop and check all cells? you cannot compare a value with a range. Example like this: msdn.microsoft.com/en-us/library/office/ff195193.aspx — Prisoner
– Prisoner, Commented Oct 27, 2016 at 8:49

user3598756 · Accepted Answer · 2016-10-27 10:42:52Z

1

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

edited Oct 27, 2016 at 10:42

answered Oct 27, 2016 at 8:54

user3598756

29.5k4 gold badges20 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Philip Connell Over a year ago

Thank you for the help. I need to check that all cells are populated. the code you gave me If WorksheetFunction.CountA(Sheets("Input").Range("A11:C100")) = Sheets("Input").Range("A11:C100").Count Then did not work it just turn the VBA area blank and did the same to the excel sheet have i done something wrong?

user3598756 Over a year ago

sorry there should be a "<" instead of a "=". see edited answer. Not clear what does "it just turn the VBA area blank" mean, though.

Philip Connell Over a year ago

@3598756: The amended code worked perfectly. Thank you so much for your time and skills. Much respect from Dublin. :-) The "It just turns blank" the VBA editor just went white as if no code was there and the excel sheet went to a grey background as if waiting to open a new file. But it is fixed now, Again thank you so much for the assistance

Philip Connell Over a year ago

Ha Ha:-) Yep. That's me Marcoing and VBAin for my life :-P

Collectives™ on Stack Overflow

Runtime Error 13 type mismatch Excel

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related