VBA Run Type Error 13 - Data Mismatch

[David Evans]

Private Sub Worksheet_Calculate()
   
    Application.ScreenUpdating = False
    Dim c As Range
    For Each c In Range("D85:AC85")
        c.EntireColumn.Hidden = (c.Value = 0)
    Next c
    Application.ScreenUpdating = True
End Sub

causes an Error 13 ...

I cannot for the life of me see it ...
Any insight gratefully received

 

[Somendra Misra]

Hi David,

When I run your code, this what happened:
Case 1: in row 85 from column D:AC if I put something like 1 or a, that column is not hidden.
Case 2: if the range is blank it is hidden.

Note this thing happened when i debug the code line by line.

But if I put say in D85 =D82 and change value in D82, nothing happened.

But I did not received any error.

Regards,

 

[David Evans]

Hi David,

When I run your code, this what happened:
Case 1: in row 85 from column D:AC if I put something like 1 or a, that column is not hidden.
Case 2: if the range is blank it is hidden.

Note this thing happened when i debug the code line by line.

But if I put say in D85 =D82 and change value in D82, nothing happened.

But I did not received any error.

Regards,

Thanks for giving it a shot - IIRC, it used to work ..... in a slightly different worksheet!!

 

[Hui]

David

Your code works fine in my Excel 2010 and 2013 versions.

Do you have numbers of calculations in row 85?
Can you share the worksheet that isn't working?

 

[shrivallabha]

In the past, I have seen lines like below

c.Value = 0

error out with RTE13 when you have a cell with excel formula error like #N/A. Please check if that is the case.

 

[David Evans]

David

Your code works fine in my Excel 2010 and 2013 versions.

Do you have numbers of calculations in row 85?
Can you share the worksheet that isn't working?

Thanks - you guys are on to it! -The range has formulas and *occasionally* fails!

When i get back in the office I'll post the offending worksheet - suitable redacted - so that we can all understand the root cause.
Appreciate the reply!

 

[David Evans]

In the past, I have seen lines like below

c.Value = 0

error out with RTE13 when you have a cell with excel formula error like #N/A. Please check if that is the case.

Yep - I think this is the cause of it - it has worked in the past ...

 

[shrivallabha]

You can wrap the line code with If statement like below so that it doesn't trip on errors.:

  If IsNumeric(c.Value) Then
  c.EntireColumn.Hidden = (c.Value = 0)
  End If

 

[Somendra Misra]

@David Evans

Hi tried it with ISERROR: see below code.

Private Sub Worksheet_Calculate()
 
    Application.ScreenUpdating = False
    Dim c As Range
   
    For Each c In Range("D85:AC85")
        If IsError(c.Value) Then
        c.EntireColumn.Hidden = False
        Else
        c.EntireColumn.Hidden = (c.Value = 0)
        End If
       
       
    Next c
    Application.ScreenUpdating = True
End Sub

Regards,

 

[David Evans]

Thanks Ninjas!
Dai
Baby Ninja