Wierd Application-defined or object-definer error on running but not when debugging

stian

New Member
Joined
Jan 9, 2014
Messages
2
I have a very wierd error I cant seem to solve.


The error is in the subject and the funny thing is when i debug it and then step through the code (with F8) it works perfectly, with no error, and continues with the expected result.
So basicly if I run the code and the error pops up, if I press Debug and then just run again, it works perfectly.


The code is pretty complicated but it fails when I try to hide a range with:
Code:
ThisWorkbook.Worksheets("Sheet1").Range(s).EntireColumn.Hidden = False


Any ideas on what can cause this?


Here is the code snipet that calculates it. Short explanation is that this is a filtering method, it looks in the datasheet "Data" and puts the value in a field there into an array, which is a text string and splits it on the ; sign.
Then it goes through each of the strings and looks for a match on row 13. If it finds a match it adds the X:X row to the s variable. The count is only for proper nesting of the string if its more than 1. Then it hides all the columns. In the For i = 1 To 30 and then the idea is to show the ones that was saved in the s variable again.
Code:
Sub Method()
Dim s As String
y = Sheet1.Cells(13, Columns.count).End(xlToLeft).Column
letter = Split(Cells(1, y).Address(True, False), "$")
x = letter(0) 'Bredde
x = "AA"
c = 0
k = Split(Application.Worksheets("Data").Range("C15").Value, ";")
For Each i In k
    txt = i
    If Not IsError(Application.Match(i, Range("A13:" & x & "13"), 0)) Then
        l = Split(Cells(1, Application.WorksheetFunction.Match(i, Range("A13:" & x & "13"), 0)).Address(True, False), "$")
        If (c > 0) Then
            s = s + ","
            s = s + l(0) + ":" + l(0)
        Else
            s = l(0) + ":" + l(0)
            c = c + 1
        End If
    End If
Next i


For i = 1 To 30
If (Sheets("Sheet1").Columns(i).Hidden = False) Then
   Sheets("Sheet1").Columns(i).Hidden = True
End If
Next i


ThisWorkbook.Worksheets("Sheet1").Range(s).EntireColumn.Hidden = False
Range("A1").Select
ActiveWindow.ScrollColumn = 1
End Sub

Here is also a screenshot of when it fails and I have clicked the DEBUG button:
p87cm1N.jpg

Showing what is saved in the s variable. So if I just click the Play button now (resume the code) it works as intended.


Any help is very much appriciated :)
 
Last edited:

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Watch MrExcel Video

Forum statistics

Threads
1,127,781
Messages
5,626,837
Members
416,204
Latest member
Perzo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top