Copy & paste cell x times (x = value of another cell)

[Bungraman]

Hi all
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Another problem and which I can’t find a solution anywhere. I have the following code:

Sub CopyData()
Dim MyRange As Range
Dim i As Long
Dim r As Range
    Set r = Range("C1001")
    Set MyRange = Range("B1000")
Application.ScreenUpdating = False
 
        Range("C1001").Select
        ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],R5C1:R999C3,3)"
 
    For i = 1 To MyRange
 
        r.Copy
        'Set MyRange = MyRange.Offset(1, 0)
        'Selection.Offset(1, 0).Select
        Set r = r.Offset(r.Rows.Count)
        ActiveSheet.Paste r
    Next i
Application.ScreenUpdating = True
End Sub

<o></o>
Cells in Column “C1001” onwards are empty. I want to put the lookup formula in this cell C1001 and copy it to the next cell down by the number of times based on the value in cell B1000. It runs but it pastes an extra formula than the amount required. For example, if B1000 = 10, only cells C1001 to C1011 should be populated with the formula, but C1012 has the formula too.
<o></o>
I must be missing something in my code.
<o></o>
Can anybody help please??

 

[AlphaFrog]

Try this...

[color=darkblue]Sub[/color] CopyData()
 
    Range("C1001").Resize(Range("B1000").Value).FormulaR1C1 = "=VLOOKUP(RC[-1],R5C1:R999C3,3)"
 
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[Bungraman]

Thanks AlphaFrog, I had no idea it would be that simple. Works perfectly.

 

[AlphaFrog]

You're welcome. Thanks for the feedback.