Worksheets loop, array

Question

I am a beginner and I would like to do a loop in all the worksheets of my excel file, performing this specific action: changing the format to all columns with a specific header. Unfortunately the code below works only in the active worksheet, and not in other worksheets in the active workbook. Do you have any clue what's wrong in it ?

Many thanks

Sub loop()

Dim timelist As Variant, sht as worksheet, rcell As Range, 
rrow As Range, t As Integer

timelist = Array("Created", "Creation")

For Each sht In ActiveWorkbook.Worksheets

Set rrow = Range("A1.Z1")
For t = LBound(timelist) To UBound(timelist)
For Each rcell In rrow
rcell.Select
if rcell.Value = timelist(t) Then
   ActiveCell.Offset(1, 0).Range("A1").Select
          Range(Selection, Selection.End(xlDown)).Select
          Selection.NumberFormat = "dd/mm/yyyy hh:mm:ss"
    End If
Next rcell
Next t
Next sht

End Sub

user_9312892 · Answer

Looping through worksheets isn’t activating the current loop woorksheet, so you have to qualify all range references to it

You may do that by means of a “With sht...End With” block and premitting a dot before all range references inside the block

And don’t “select” anything but just use range objects

For Each sht In ActiveWorkbook.Worksheets
    With sht 
        Set rrow = .Range("A1:Z1")
        For t = LBound(timelist) To UBound(timelist)
            For Each rcell In rrow
                If rcell.Value = timelist(t) Then
                    .Range(rcell.Offset(1, 0), rcell.Offset(1, 0).End(xlDown)).NumberFormat = "dd/mm/yyyy hh:mm:ss"
                End If
            Next rcell
        Next t
    End With
Next sht

Worksheets loop, array

1 Answers