Macro isn't Looping Through Workbooks As Expected

Question

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

You are missing the dots before `.Range(....`. For example (after the for i = 1 to iLastRow) it should be `.Range("B" & i).Select`. Yet, it would be even better to use `.Range("B" & i).Formula = 'these are formulas`. — Ralph, Jun 17 '16 at 15:19
Also, I highly **highly** recommend reading through [How to avoid using `.Select](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros). In short, you can delete any `.Select` text and back up the `Selection.` put to where `Select` was...so you could do `.Range("B" & i).Formula = ...`. — BruceWayne, Jun 17 '16 at 15:22

score 4 · Accepted Answer · answered Jun 17 '16 at 15:23

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:

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

Macro isn't Looping Through Workbooks As Expected

1 Answers1