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:

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:

Forum statistics

Threads
1,085,838
Messages
5,386,254
Members
401,988
Latest member
Sherzodom

Some videos you may like

This Week's Hot Topics

Top