VBA error handling

[450nick]

I've got a sub which does some things with a dynamic range. If there is nothing in the dynamic range though, I want it to ignore those steps. Is there a vba equivalent of "iserror" to use with an if command?

 

[Andrew Poulsom]

What do you mean by "nothing in the dynamic range"?

 

[450nick]

As in, if the dynamic range finds no data and therefore returns an error in the script.

Its basically to select the contents of a table. But in some cases, the table can contain nothing but empty space, which will trip up the script. I just need it to realise that nothing is there, and using an if statement, go to the next step in the routine.

 

[Andrew Poulsom]

You can also use the CountBlank WorksheetFunction to count the number of blank cells in a range, eg:

MsgBox WorksheetFunction.CountBlank(Range("A1:A20"))

 

[450nick]

I'm not quite following, could I use that to make that part of the subroutine be bypassed if the table is empty? Or would you use that to carry on with the subroutine but make the range work with blanks?

 

[Jon von der Heyden]

Do you mean that you have a lookup value and it throws an error when it is not found in the range? You can also use IsError in VBA, e.g:

If IsError(Application.Match("Something",Range("MyDyRange"),0)) Then
    goto Skip
Else
    'do something if there is a match
End If
 
Skip:
    'Rest of code here

 

[450nick]

Not quite, its this specific bit of code i'm refering to:

Sheets("Data_Entry").Range("_CopyTable").Select
Selection.Copy

The dynamic range is formed of:

_CopyTable1:
=MAX(MATCH(REPT("Z",255),Data_Entry!$B$9:$B$65536),MATCH(REPT("Z",255),Data_Entry!$C$9:$C$65536), MATCH(REPT("Z",255),Data_Entry!$D$9:$D$65536), MATCH(REPT("Z",255),Data_Entry!$E$9:$E$65536))

_CopyTable2:
=MAX(MATCH(REPT("Z",255),Data_Entry!$F$9:$F$65536),MATCH(REPT("Z",255),Data_Entry!$G$9:$G$65536),MATCH(REPT("Z",255),Data_Entry!$H$9:$H$65536))

_CopyTable:
=Data_Entry!#REF!:INDEX(Data_Entry!$H$9:$H$65536,MAX(_Copytable1,_Copytable2))



If "_CopyTable" contains no data, then the script fails with an "out of range" error...

Pehaps the dynamic range data can be modified?

 

[Jon von der Heyden]

Why is the starting reference of the range '#REF!'? Did you delete the row/column? Perhaps you should fix the starting range reference using INDIRECT worksheet function.

E.g:
=INDIRECT("Data_Entry!A1"):INDEX(Data_Entry!$H$9:$H$65536,MAX(_Copytable1,_Copytable2))

 

[450nick]

Oops, sorry that's meant to be $B$9, not Ref!

Same problem though

 

[Jon von der Heyden]

I can't replicate your error. There shouldn't be any reason for an error to occur if the range is empty. If it is a valid range then it should still copy. Andrew has already suggested how to avoid copying the range if it is empty.

Maybe as a more generic way of handling the error you could try the code below but personally I would still rather know why the error occurts and handle it directly.

On Error GoTo Skip
    Sheets("Data_Entry").Range("_CopyTable").Copy
        'paste the range somewhere
On Error GoTo 0
Skip:
    'error trying to copy range, do something else