Miss something in simple loops

Question

I have a range of cell in one sheet I wish copy n times in another sheet: for n=4 one would have:

Input (Sheet1)              Output (Sheet2)
 ABC                           ABC
 DEF                           ABC
 GHI                           ABC
                               ABC
                               DEF
                               DEF
                               DEF
                               DEF
                               GHI
                               GHI
                               GHI
                               GHI

What I did is simply:

Sub REPLICATE()
  Dim i As Integer
  Dim j As Integer
  Dim TEMP As String

  For i = 1 To 400
    TEMP = Workbooks("Libro1").Sheets("Hoja1").Cell(i, 1).Value
      For j = 1 To 4
        Workbooks("Libro1").Sheets("Hoja2").Cell(j, 1) = TEMP
      Next j
  Next i
End Sub

And I got the error 438. I thought the problem could have been that I use string, but I tried with simple Integer in my Sheet1, and I've gotten the same problem. Thanks for advices.

user_7008044 · Answer

It's Cells not Cell. In any case, your code won't work because it will overwrite (j restarts at 1 every time). Try this instead (adjust sheet references to suit).

Sub REPLICATE()

Dim i As Long

For i = 1 To Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
    Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp)(2).Resize(4).Value = Sheet1.Cells(i, 1).Value
Next i

End Sub

user_1188513 · Answer

The Sheets collection returns an Object, which means anything you type after accessing it will not give you IntelliSense or auto-complete assistance, and VBA will happily compile it, because any call made against the Object interface is late-bound and therefore only resolved at run-time.

The solution is to stick to early-bound code and declare a Worksheet object. Also, use the Worksheets collection if you mean to pull a Worksheet, since the Sheets collection can give you Chart objects (i.e. not all sheets are worksheets).

Dim ws As Worksheet
Set ws = Workbooks("Libro1").Worksheets("Hoja1")

And now when you type ws. you'll see that Cell is indeed not a member of the Worksheet type (hence "Object doesn't support property or method"):

For i = 1 To ws.Cells(...)

That said if Workbooks("Libro1") is ThisWorkbook (the book that contains the VBA code), then you should be using the existing worksheet object instead.

You get a "free", global-scope Worksheet object variable for each Worksheet document module in your VBA project - just use it!

For i = 1 To Sheet1.Cells(...)

Like SJR's answer does.

You can rename that global-scope object variable by finding the sheet in the Project Explorer (Ctrl+R), and then changing its (Name) property in the properties toolwindow (F4). Note, changing the name like this will not automatically update your existing code that uses Sheet1. If you want to rename and propagate the change, you need to refactor your code. Rubberduck (an open-source VBIDE add-in project I manage) can do that for you:

Right-click the worksheet in Rubberduck's Code Explorer:

Supply a new name:

Every reference to the old name across the entire code is now updated:

You can also try performing that refactoring with Ctrl+H "Find/Replace", but that can be tedious, and risks introducing more issues. Rubberduck builds a symbol table for this, so it's much more robust than a simple find & replace.

Miss something in simple loops

2 Answers