CODEWITHSUNDEEP
vbaexcel
Rob
Rob

Reputation: 85

Macro isn't Looping Through Workbooks As Expected

I'm trying to write a macro that inserts formulas into columns B-F in all worksheets in a workbook. For some reason the workbook loop isn't working and the macro just continuously runs in the first workbook. Anyone have any ideas?

Option Explicit
Sub Formuoli()
Dim iLastRow As Integer
Dim i As Integer
Dim ws As Worksheet
    iLastRow = Range("a10000").End(xlUp).Row
For Each ws In ThisWorkbook.Worksheets
    With ws
        For i = 1 To iLastRow
            Range("B" & i).Select
            Selection.Formula = 'these are formulas
            Range("C" & i).Select
            Selection.Formula = 'these are formulas
            Range("D" & i).Select
            Selection.Formula = 'these are formulas
            Range("E" & i).Select
            Selection.Formula = 'these are formulas
            Range("F" & i).Select
            Selection.Formula = 'these are formulas
        Next i
    End With
Next ws
End Sub

Upvotes: 2

Views: 100

Answers (1)

Ralph
Ralph

Reputation: 9434

You missed a few dots before the Range functions:

Option Explicit

Sub Formuoli()

Dim iLastRow As Integer
Dim i As Integer
Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
    With ws
        iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        For i = 1 To iLastRow
            .Range("B" & i).Formula = "" 'these are formulas
            .Range("C" & i).Formula = "" 'these are formulas
            .Range("D" & i).Formula = "" 'these are formulas
            .Range("E" & i).Formula = "" 'these are formulas
            .Range("F" & i).Formula = "" 'these are formulas
        Next i
    End With
Next ws

End Sub

Changes:

  1. Added the required dots.
  2. Moved iLastRow into the loop to determine the last row for each sheet.
  3. Combined several rows by removing the Select.

Upvotes: 4

Related Questions