CODEWITHSUNDEEP
excelvba
Ken Ingram
Ken Ingram

Reputation: 1598

Loop to Insert Row, Copy data

I give up. I just spent four hours trying to figure out why this macro will not work. I want it to take the given source Range, cycle through it using the For Loop and copy the Value to a different column.

I want it to start at the given destination cell and

I can't figure out why

Note that I'm not looking for clever, elegant solutions to the problem, in an effort to teach myself the paradigm, I want to keep things really basic. As I get better at understanding the basics, I'll try some advanced tricks.

TIA

Sub Macro1()
Dim firstRow As Range, lastRow As Range
Dim source As Range, destination As Range
Dim readCell As Range

Set firstRow = Range("F2")
Set lastRow = Range("F20")
Set destination = Range("A21")


Set source = Range(firstRow.Address(False, False) & ":" & lastRow.Address(False, False))

For Each readCell In source
    destination.Select
    destination.Value = readCell.Value

    If (readCell.Address(False, False) = lastRow.Offset(1, 0)) Then
        Exit For
    Else
       'destination.Select
    End If

    'MsgBox (destination.Value)
    destination.EntireRow.Insert Shift:=xlUp
    Set destination = destination.Offset(1, 0)
Next
End Sub

Upvotes: 2

Views: 6138

Answers (2)

brettdj
brettdj

Reputation: 55682

Very commendable that you want to understand as well as solve

It is easier to use a row counter than increments from a fixed destination. This minor adjustment

  • avoids Select

  • uses a counter, lngRow, to control the new row and new values

    code

    Sub Macro1()
Dim readCell As Range
Dim lngRow As Long
For Each readCell In Range("F2:F20")
    [a21].Offset(lngRow, 0).EntireRow.Insert Shift:=xlUp
    [a21].Offset(lngRow, 0).Value = readCell.Value
    lngRow = lngRow + 1
Next
End Sub

Upvotes: 1

chris neilsen
chris neilsen

Reputation: 53137

Here's some hints:

Given that firstRow and lastRow are single cells, no need for the Address stuff. Use

Set source = Range(firstRow,  lastRow)

In destination.EntireRow.Insert Shift:=xlUp, because you are applying Insert to an entire row, Shift makes no difference. Use

destination.EntireRow.Insert

The inserted row in placed above destination, and destination is shifted down. So the first iteration of the for loop does this

  1. Set destination to A21
  2. Insert row, shifting destination to A22
  3. Set desination down one row, ie A23

The next iteration will then overwrite the data originally in A22, now in A23

I think you want

Sub Macro1()
    Dim firstRow As Range, lastRow As Range
    Dim destination As Range
    Dim readCell As Range

    Set firstRow = Range("F2")
    Set lastRow = Range("F20")
    Set destination = Range("A21")

    For Each readCell In Range(firstRow, lastRow)
        destination.Value = readCell.Value
        destination.EntireRow.Offset(1, 0).Insert
        Set destination = destination.Offset(1, 0)
    Next
End Sub

Upvotes: 1

Related Questions