VBA Code doesnt work when pressing the button but working when inside code module.

[romanemul]

Hi forum members.

Macro is running fine from inside module. It stops on certain line .

Point of this code:
Im trying to change workbook generated by another application by prepending a number to certain lines after inserting a new column.

Problem line :

cell.Offset(2, -1).Select

Sub formatter()

Dim rng As Range

Dim cell As Range
Dim daterange As Range
Dim countrange As Range
Dim datacell As Range


Dim wb As Workbook
Dim ws As Worksheet
Dim actwb As Workbook
Dim workbookname As String
Dim xlapp As Application


Set xlapp = GetObject(, "Excel.Application")

workbookname = InputBox("Number of book ?")

Set wb = xlapp.Workbooks("Book" & workbookname)
Set ws = wb.Worksheets("Sheet1")
Set rng = ws.Range("a:a")

rng.Cells.Columns.Insert (1)

Set cell = ws.Range("A1")


For Each cell In ws.Range("b:b")
    
    If cell.Interior.Color = black Then
        Set countrange = cell.Offset(2, -1)
        
        Set daterange = Range(cell.Offset(2, 8), cell.Offset(2, 8).End(xlDown))
        
        cell.Offset(2, -1).Select    ' <------stops working here
                    
            If daterange.Count > 500 Then End
            
                

                counter = 0
                For Each datacell In daterange
                
                    If Left(datacell.Text, 5) = Left(datacell.Offset(1, 0).Text, 5) Or datacell.Offset(1, 0) = "" Then
                        countrange.Value = counter + 1
                        counter = counter + 1
                        Set countrange = countrange.Offset(1, 0)


                    Else
                        countrange.Value = counter + 1
                        counter = 0
                        Set countrange = countrange.Offset(1, 0)
                    
                    End If
                Next datacell
            
    End If
Next cell


Set rng = Nothing
Set wb = Nothing
Set ws = Nothing


End Sub

I read about external referencing of cells and i think i have my cell referenced properly
at

Set cell = ws.Range("A1")

Can anybody help me ?

Thanks

 

[footoo]

You can only select a cell on the active sheet.

 

[RoryA]

You could just remove that line as it doesn't appear to serve any purpose.

 

[romanemul]

but when im stepping over the code everything is fine.

 

[romanemul]

You could just remove that line as it doesn't appear to serve any purpose.

Nice one Rory

Thank you

 

[RoryA]

Glad to help.