Im trying to loop through specific sheets and within that sheet change the values of column A starting from cell A2 to value 1 and onwards.

For example cell A2=1, cell A3=2 and goes on

This is my code

dim wksheets as variant
Dim wksheet as long
Dim ws as worksheet 

Wksheets = array("apple", "pear", "orange")

for wksheet = lbound(wksheets) to ubound(wksheets)
    Set ws = worksheets(wksheets(wksheet))
    Dim endrow as integer
    Dim i as integer

    With ws
      .activate
       Endrow = .cells(rows.count, “A”).end(xlup).row
       For i = 2 to endrow
         If .cells(i,”A”).value <> “” then
             .cells(i,”A”).value = i-1
         End if
        Next i 
    Msgbox “done”
    end with
next wksheet

But the problem is, it isnt looping at all! I only received one done msgbox then the excel is stuck loading endlessly. What went wrong with my code? Help please :(

Im trying to loop through specific sheets and within that sheet change the values of column A starting from cell A2 to value 1 and onwards.

For example cell A2=1, cell A3=2 and goes on

This is my code

dim wksheets as variant
Dim wksheet as long
Dim ws as worksheet 

Wksheets = array("apple", "pear", "orange")

for wksheet = lbound(wksheets) to ubound(wksheets)
    Set ws = worksheets(wksheets(wksheet))
    Dim endrow as integer
    Dim i as integer

    With ws
      .activate
       Endrow = .cells(rows.count, “A”).end(xlup).row
       For i = 2 to endrow
         If .cells(i,”A”).value <> “” then
             .cells(i,”A”).value = i-1
         End if
        Next i 
    Msgbox “done”
    end with
next wksheet

But the problem is, it isnt looping at all! I only received one done msgbox then the excel is stuck loading endlessly. What went wrong with my code? Help please :(

• excel
• vba
• loops

• Do you have Option Explicit at the top of your module? If not, does anything change if you add it? – Tim Williams Commented Jan 31 at 16:14
• Can you you copy/paste from the Visual Basic Editor (VBE) your exact code. The double quotes “ (curly) should be " (straight) – CDP1802 Commented Jan 31 at 16:14
• Thanks for the reply. Im not at my desk now.. but it is straight code (dont think the keyboard has curly quotes). Will double check when im at my desk. is the logic of the code correct? Im lost if this doesnt solve the problem :( does this code work on ur end? – 진애슐린 Commented Jan 31 at 16:25
• 1 Works for me once the quotes are fixed. You should probably change those integer types to long as well. – Warcupine Commented Jan 31 at 16:27
• You do have data in column A on each sheet right? it won't do anything if there isn't. – Warcupine Commented Jan 31 at 16:28

2 Answers 2

Try this:

Dim wksheets As Variant
Dim wksheet As Long
Dim ws As Worksheet

wksheets = Array("apple", "pear", "orange")

For wksheet = LBound(wksheets) To UBound(wksheets)
    Set ws = Worksheets(wksheets(wksheet))
    Dim endrow As Integer
    Dim i As Integer

    With ws
       endrow = .Cells(Rows.Count, 1).End(xlUp).Row
       For i = 2 To endrow
         If .Cells(i, 1).Value <> "" Then
             .Cells(i, 1).Value = i - 1
         End If
        Next i
    End With
Next wksheet
MsgBox "done"

Note:

Ideally, your message box indicating "Done" should be out of the loop

Add an Integer Sequence to a Column

• You could simplify (get rid of the inner loop) and increase efficiency using the following.

Option Explicit

Sub AddIntegerSequence()
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code

    Dim SheetNames() As Variant: SheetNames = Array("Apple", "Pear", "Orange")
    
    Dim ws As Worksheet, rg As Range, w As Long, RowCount As Long
    
    For w = LBound(SheetNames) To UBound(SheetNames)
        Set ws = wb.Sheets(SheetNames(w))
        With ws.Range("A2")
            RowCount = ws.Cells(ws.Rows.Count, .Column).End(xlUp).Row - .Row + 1
            If RowCount > 0 Then
                Set rg = .Resize(RowCount)
                rg.Value = ws.Evaluate("ROW(1:" & RowCount & ")")
                MsgBox "Done (" & ws.Name & ").", vbInformation
            Else
                MsgBox "No data (" & ws.Name & ")!", vbExclamation
            End If
        End With
    Next w

End Sub