Type mismatch error in excel VBA

Question

Need some advice, getting this error when I try run this macro i get a mismatch error.

The "InsertNAtoBlanks" is self explanatory, I just need this IF statement to read a specific column in that table and if there are blanks in any of the cells run the "InsertNAtoBlanks" macro, if not then move on to the next step in the macro.

Dim x As ListObject
Dim z As Range
Dim Bcell As Range

Set x = ActiveSheet.ListObjects("Table6")
Set z = x.DataBodyRange.Columns(11)

For Each Bcell In z
If Bcell.Value <> Empty Then

Run "InsertNAtoBlanks"

 Else

'Contiue on with macro

Answer 1

if there are blanks

Change

If Bcell.Value <> Empty Then

to

If Bcell.Value <> "" Then

Or to

If Len(Trim(Bcell.Value)) <> 0 Then

The Empty keyword is used as a Variant subtype. It indicates an uninitialized variable value.

Your code can be written as

Sub Sample()
    Dim x As ListObject
    Dim z As Range
    Dim Bcell As Range

    Set x = ActiveSheet.ListObjects("Table6")
    Set z = x.DataBodyRange.Columns(11)

    For Each Bcell In z.Cells
        If Bcell.Value <> "" Then
            Run "InsertNAtoBlanks"
        End If
    Next
End Sub