Macro for Excel 2007 (Copy Paste & Loop)

[stuttgarter]

I am new to Macros and trying to realize the following steps, but in vain

1. Copy the content of cells A1:B5 with formatting
2. Paste it in A6
3. Iterate n number of times
4. n reads the value written in cell D1

any suggestings would be highly appreciated.

 

[Andrzejek]

A place to start:

Dim i As Integer
Dim x As Integer

Range("A1:B5").Copy

For i = 1 To Range("D1").Value
    If i = 1 Then
        x = i * 6
    Else
        x = (i * 6) - (i - 1)
    End If
    Range("A" & x).Select
    ActiveSheet.Paste
Next i

Have fun.

---- Andy

 

[Andrzejek]

Well, on the second look - you don't need this part:

    [s]If i = 1 Then[/s]
        [s]x = i * 6[/s]
    [s]Else[/s]
        x = (i * 6) - (i - 1)
   [s] End If[/s]

the [tt] - (i - 1)[/tt] is to eliminate empty rows.


Have fun.

---- Andy

 

[stuttgarter]

Incredible Andy. Thanks a lot. You have written very clean code.
It took 10 hours to hit and try (still unsucessful) and your few lines did it on a fly.

I am trying to dig a little deeper

Step 1 (Done with Andy's help):
1. Copy the content of cells A1:B5 with formatting
2. Paste it in A6
3. Iterate n number of times
4. n reads the value written in cell D1

Step 2:
1. Column "E" contains roll number of students
2. Column "F" contains marks obtained by students
3. Cell D1 (counts number of students in column E) --> No. of iteration [highlight #4E9A06]done using count function in excel[/highlight]
4. Fill Roll no. in cell B2
5. Fill Marks obtained in cell B5
6. Same pattern for all students roll number and marks

Any suggestion/guidance will be highly appriciated.

 

[stuttgarter]

I am using the following code but i feel its not the optimal one as same work is done by three for loops. Any suggestings to optimize it?

Dim i As Integer
Dim x As Integer
Dim k As Integer
Dim l As Integer
Dim m As Integer
Dim n As Integer

Range("A1:B5").Copy

For i = 1 To (Range("D2").Value) - 1
    
        x = (i * 6) - (i - 1)
    
    Range("A" & x).Select
    ActiveSheet.Paste
Next i

k = 2
l = 2
For i = 1 To Range("D2").Value
    
    Range("E" & k).Copy
    Range("B" & l).Select
    ActiveSheet.Paste
    k = k + 1
    l = l + 5
Next i

m = 2
n = 5
For i = 1 To Range("D2").Value
    
    Range("F" & m).Copy
    Range("B" & n).Select
    ActiveSheet.Paste
    m = m + 1
    n = n + 5
Next i

 

[Andrzejek]

It is hard to figure out your logic and your data in your spreadsheet here, but wouldn’t be better to do Step 2 before Step 1? (From your post from 5 Feb 13 9:13 )

Just a guess here…



Have fun.

---- Andy

 

[zelgar]

Here's a very optimized version of your code:

Dim iend, i As Integer
iend = Cells(2, 4)
For i = 1 To iend
    Range(Cells(1, 1), Cells(5, 2)).Copy _
        Destination:=Cells(1 + (i - 1) * 5, 1)
    Cells(1 + i, 5).Copy _
        Destination:=Cells(2 + (i - 1) * 5, 2)
    Cells(1 + i, 6).Copy _
        Destination:=Cells(5 + (i - 1) * 5, 2)
Next i

 

[Andrzejek]

There is always a better way to do it

Just one point:

Dim iend [red]As Integer[/red], i As Integer

Otherwise [tt]iend[/tt] is a Variant

Have fun.

---- Andy

 

[stuttgarter]

Thank you Andy and Zelgar. Its working exactly the way i wanted.

I am just wondering "How long i will take to develop such logic" !!