Structured References without converting to Table

[eli_m]

Hi All,

I am currently using the Data>Filter to make my data look like a table.
The reason I don't like using a table is that I get errors when the data isn't consistent across the column etc.

To combat this in the past I used

Dim r As Range: Set r = Range("A2:AY200")
Dim cel As Range

For Each cel In r
    With cel
        .Errors(8).Ignore = True 'Data Validation Error
        .Errors(9).Ignore = True 'Inconsistent Error
        .Errors(6).Ignore = True 'Lock Error
    End With
Next cel

This code helped me ignore all the errors that popped up in my table but put a massive strain on my CPU as it calculates after every Worksheet_Calculate() and Worksheet_Change(ByVal Target As Range).

it was very very slow on laptops as well but it worked "perfectly"

Is there a way to use structure references or something similar so I can stop using Tables?

I love that every code in my column is like eg:

=IF(ISBLANK([@[Surgery Date]]),"",
IF(NOT(ISNUMBER([@[Surgery Date]])),"Surgery Date TBC",
IF(AND(ISNUMBER([@[Pre-Book Set]]),ISNUMBER([@[Surgery Date]])),IFERROR(IF(AND(MONTH([@[Surgery Date]])=12,DAY([@[Surgery Date]])=25),"Surgery Date TBC",
"Create Calandar Invite"),""),"")))

Instead of every row formula being different because it uses 'B2' and 'H2' etc as the above is much easier to read:

=IF(ISBLANK(B2),"",
IF(NOT(ISNUMBER(B2)),"Surgery Date TBC",
IF(AND(ISNUMBER(H2),ISNUMBER(B2)),IFERROR(IF(AND(MONTH(B2)=12,DAY(B2)=25),"Surgery Date TBC",
"Create Calandar Invite"),""),"")))

Any help would great! Thanks in advance

 

[ChrisFoster]

I think just adding Application.EnableEvents will help.

Sub IgnoreErrorsInTable()
    Dim tbl As ListObject: Set tbl = Sheet1.ListObjects("MyTable")
    Dim r As Range: Set r = tbl.DataBodyRange
    Dim cel As Range

    Application.EnableEvents = False 'Disable events to speed up code execution

    For Each cel In r
        With cel
            .Errors(8).Ignore = True 'Data Validation Error
            .Errors(9).Ignore = True 'Inconsistent Error
            .Errors(6).Ignore = True 'Lock Error
        End With
    Next cel

    Application.EnableEvents = True 'Enable events again

End Sub

 

[Fluff]

Is there a way to use structure references or something similar so I can stop using Tables?

Not really, if you want to use structured references then you need to use tables.
You could possibly use named ranges, but that can cause problems in itself.

 

[eli_m]

Not really, if you want to use structured references then you need to use tables.
You could possibly use named ranges, but that can cause problems in itself.

that's a shame as I am not a fan of having my data in a table due to all the errors (Data Validation/Inconsistent/Lock errors) but I really love the structured references.

I think just adding Application.EnableEvents will help.

Sub IgnoreErrorsInTable()
    Dim tbl As ListObject: Set tbl = Sheet1.ListObjects("MyTable")
    Dim r As Range: Set r = tbl.DataBodyRange
    Dim cel As Range

    Application.EnableEvents = False 'Disable events to speed up code execution

    For Each cel In r
        With cel
            .Errors(8).Ignore = True 'Data Validation Error
            .Errors(9).Ignore = True 'Inconsistent Error
            .Errors(6).Ignore = True 'Lock Error
        End With
    Next cel

    Application.EnableEvents = True 'Enable events again

End Sub

I'll give this a go and see if it helps thanks!

 

[Fluff]

I am not a fan of having my data in a table due to all the errors

That makes two of us.