Object doesn't support this property or method VBA Loop not ending

Question

Hi all I am very new to the vba scene! I am having trouble with this code. I have been trying to work out what is happening with it... When I press F8 there is no error code coming up but it keeps on looping in the if statements

Sub testing()
Dim ws As Worksheet
Dim xRow As Integer
xRow = 1


With Me
    .Columns(1).ClearContents
    .Cells(1, 1) = "ROLES"
    .Cells(1, 1).Name = "Roles"
End With

For Each ws In Application.Worksheets
    If ws.Name <> Me.Name Then
        xRow = xRow + 1
        With ws
            .Range("A1").Name = "Start_" & ws.Index
        End With
    End If
Next
Application.Sceeenupdating = True

End Sub

Use F5 instead of F8 to run through the entire subroutine — Cody Geisler
– Cody Geisler, Commented Jun 26, 2017 at 14:23
@CodyG It comes up with this error- 'Object doesn't support this property' when I press f5 — Zoe Chu
– Zoe Chu, Commented Jun 26, 2017 at 14:27
Are you sure your code is working. Visually I can see that It will fail on With Me and If ws.Name <> Me.Name Then and then on Application.Sceeenupdating = True — Siddharth Rout
– Siddharth Rout, Commented Jun 26, 2017 at 14:27
@SiddharthRout Um when I tried to debug it using f8, it goes past that part fine. — Zoe Chu
– Zoe Chu, Commented Jun 26, 2017 at 14:27
Learning debugging - which line of code has the issue when you press F5 ? There are a few ways to do this. I would comment out the .Range line. Or I would put a print statement at the .Range line with the xRow and ws name — Cody Geisler
– Cody Geisler, Commented Jun 26, 2017 at 14:30

Siddharth Rout · Accepted Answer · 2017-06-26 14:38:03Z

2

Are you sure your code is working. Visually I can see that It will fail on With Me and If ws.Name <> Me.Name Then and then on Application.Sceeenupdating = True – Siddharth Rout 11 mins ago

So you want the names of all the sheets in Col 1 of Sheet1? – Siddharth Rout 2 mins ago edit

@SiddharthRout yes exactly – Zoe Chu 13 secs ago

Is this what you are trying?

Sub testing()
    Dim ws As Worksheet, wsMain As Worksheet
    Dim xRow As Long

    '~~> This is the sheet which will have sheet names
    '~~> in Col 1 ("A")
    Set wsMain = ThisWorkbook.Sheets("Sheet1")

    Application.ScreenUpdating = False

    With wsMain
        .Columns(1).ClearContents
        .Cells(1, 1) = "ROLES"
        .Cells(1, 1).Name = "Roles"

        xRow = 2

        For Each ws In ThisWorkbook.Worksheets
            If ws.Name <> wsMain.Name Then
                .Cells(xRow, 1).Value = ws.Name
                xRow = xRow + 1
            End If
        Next
    End With

    Application.ScreenUpdating = True
End Sub

answered Jun 26, 2017 at 14:38

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Zoe Chu Over a year ago

Hiya, its now highlighting the 'Application.Screenupdating=True' now

Siddharth Rout Over a year ago

I tested the code before posting :) There is nothing wrong with that line.

Zoe Chu Over a year ago

Man.. What am I missing

Siddharth Rout Over a year ago

Where have you pasted the code? And did you make any changes to my code?

Zoe Chu Over a year ago

Thanks again! @SiddharthRout

Cody Geisler · Accepted Answer · 2017-06-26 14:38:12Z

1

Application.Sceeenupdating = True

should be

Application.ScreenUpdating = True

This would be a little more obvious if you set the following option in the VBA editor:

Tools > Options> Set Error Trapping to "Break on All Errors"

Which should then pop up with

Clicking debug gets you to...

answered Jun 26, 2017 at 14:38

community wiki

Cody Geisler

1 Comment

Zoe Chu Over a year ago

Ahh! Okay. I thought I was going crazy when people were talking about a 4 button error message box.

Collectives™ on Stack Overflow

Object doesn't support this property or method VBA Loop not ending

2 Answers 2

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related