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.

Answer 1

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