Mismatch error in VBA

[willow1985]

Hello,

I have the below code and I am still very new to vba, but I cannot see where I went wrong...I get a mismatch error on the first line. What I am trying to write is if there are any blank cells between A2 and J2 that it will prompt the msg box and end the macro and not proceed to updating the Batch Log spreadsheet. I hope someone can shed some light on the error.

Thank you

Code:

If ThisWorkbook.Sheets("New BL Data").Range("A2:J2").Value = "" Then
MsgBox "Please Complete all Fields"
Exit Sub
End If
Range("A2:I2").Select
Selection.Copy
Sheets("Batch Log").Select
Range("BLTable").Cells(1, 1).End(xlDown).Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("New BL Data").Select
Range("A1").Select


'
End Sub

 

[Fluff]

You cannot check multiple cells for a value like.
Are the blank cells genuinely blank/empty or do they contain a formula that returns ""

 

[willow1985]

2 Cells did contain a formula (A2 and J2), so I changed it to apply to B2 to I2 (non formula cells). I am testing it and filled out some cells and left other blank in that range. I want it to not allow the macro to run if any cells between B2 and I2 are blank.
I am also having trouble with the macro unprotecting Sheet: Batch Log and protecting it again after the update. See new code:

If ThisWorkbook.Sheets("New BL Data").Range("B2:I2").Value = "" Then
MsgBox "Please Complete all Fields"
Exit Sub
End If
Range("A2:I2").Select
Selection.Copy
Sheets("Batch Log").Select
Columns("A:I").Select
Range("BLTable[[#Headers],[TYPE]]").Activate
Selection.Locked = False
Selection.FormulaHidden = False
Range("BLTable").Cells(1, 1).End(xlDown).Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("A:I").Select
Range("BLTable[[#Headers],[TYPE]]").Activate
Selection.Locked = True
Selection.FormulaHidden = False
Range("BLTable").Cells(1, 1).End(xlDown).Offset(1).Select
Sheets("New BL Data").Select
Range("A1").Select


'
End Sub

 

[Fluff]

Try

With ThisWorkbook.Sheets("New BL Data")
   If Application.CountIf(.Range("B2:I2"), "") > 0 Then
      MsgBox "Please Complete all Fields"
      Exit Sub
   End If
End With

 

[willow1985]

That worked. Now my only problem is the error I get at the Selection.Locked = False portion of the code.
Error: 1004, unable to set the locked property of the range class

PS the locked sheet is not password protected.

 

[Fluff]

Try unprotecting the sheet first.
If that doesn't work, you will need to start a new thread.

 

[willow1985]

Isnt that what the below is supposed to do, unprotect the sheet?

Columns("A:I").Select
Range("BLTable[[#Headers],[TYPE]]").Activate
Selection.Locked = False
Selection.FormulaHidden = False

 

[willow1985]

I posted a new thread.

 

[Fluff]

Nope, there is nothing in there to unprotect the sheet.