0

I am trying to copy paste values repeatedly depending on the cell value. I am having problem in my cell range where I would paste the copied value. I used the Do Until Iteration.

I am having trouble in this line: Range("B1:B(var)").Select

How can I used the cell value as the ending range of my selected cell?

For example, E1 value is 10. I want it to copy 10 ten times and be pasted only from B1:B10

Sub copy_loop()

Dim var As Integer

var = Range("E1").Value
i = 1

Do Until i > var
Range("A1").Select
Selection.Copy
Range("B1:B(var)").Select
ActiveSheet.Paste
Loop

End Sub
JMP
  • 4,417
  • 17
  • 30
  • 41
jshvt17
  • 69
  • 8
  • **1.** It should be `Range("B1:B" & var).Select` **2.** You may also want to see [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). **3.** Be careful with `Do Until i > var`. You will end up into an endless loop. **4** Since you are working with Excel rows, I recommend using `Long` instead of `Integer`/ – Siddharth Rout Aug 18 '21 at 07:30
  • But **where do ou want pasting it**. In your way it will be pasted over the existing range and you will stay in an infinite loop, not incrementing i variable. We know only where from you want pasting. Please, edit your question and better explain **in words** what you try accomplishing. – FaneDuru Aug 18 '21 at 07:32

3 Answers3

1

Please, try the next code. It will paste the range B1:B10 (if E1 value = 10) ten times starting from F1 and going on to G, I, H and so on. No any selection needed:

Sub copy_loop()
Dim var As Long, i As Long

var = Range("E1").Value 'supposing it is 10...
i = 1

Do Until i > var
    Range("B1:B" & var).Copy cells(1, 5 + i)
    i = i + 1
Loop
End Sub

Do you want pasting in column A:A, each ten rows range below the each other? You did not say anything about where to paste the range...

FaneDuru
  • 38,298
  • 4
  • 19
  • 27
1

You don't even need a loop to do this, as you can see here:

Sub copy_loop()

Dim var As Integer

var = Range("E1").Value
Range("B1:B" & var).Value = Range("A1").Value

End Sub

The trick is that you can say for an entire range (like "B1:B20") that all of them need the same value, without even needing a loop.

Dominique
  • 16,450
  • 15
  • 56
  • 112
1

Your tags say excel-formula, so paste this into B1:

=TRIM(UNIQUE(A1&REPT(" ",SEQUENCE(E1))))

A1 is your text, and E1 is the number of repeats.

UNIQUE differentiates between abc and abc (with a space at the end), so a different number of spaces is added to each text string. UNIQUE then spills, and TRIM removes any excess spaces.

JMP
  • 4,417
  • 17
  • 30
  • 41
  • Is it possible to do this in a loop? Let's say may first value of E1 is 10, it will be add from rows 1-10, then the value of E2 is 2, then it will be added from rows 11-13. – jshvt17 Aug 19 '21 at 02:33