For/Next Loop

[ctc005]

Hi all:

I'm told a for/next loop is what I want to use to accomplish this but I don't know where to start. Here goes:

I have a row in my excel, that based on an input I want to be repeated. So let's say the row is row 10 and I want it repeated based on an input in cell E5. The input in cell E5 would be for a "how many" input. So if they put 5 in E5, I need row 10 to be repeated 4 times, plus the original for a total of ten rows. Any help is appreciated

 

[Jonmo1]

How do you get "Repeated 9 times" from user entry of 5 ???

That makes no sense??

 

[ctc005]

sorry for some reason i had 5 and 10 mixed up. It would be 9 for an input of 10 and 4 for an input of 5. My bad

 

[ctc005]

I have a row in my excel, that based on an input I want to be repeated. So let's say the row is row 10 and I want it repeated based on an input in cell E5. The input in cell E5 would be for a "how many" input. So if they put 5 in E5, I need row 10 to be repeated 9 times, plus the original for a total of ten rows.

Hello ctc005,
A couple questions to clarify just what you want to do.
1.) What exactly do you mean by "I want it repeated. . . " ?
2.) By "if they put 5 in E5, I need row 10 to be repeated 9 times, plus the original for a
total of ten" - Is this because it was on row 10 to begin with?
(ie, what if the user were on row 15 and they enter a 6 in E5?)

I corrected my original post it should be repeated (ie like a fill down) same function as my doing it by hand but I want a macro to do it so it is user friendly. So if hey input 5, then row 10 would be "autofilled" down four rows for a total of 5, like the user wanted.

 

[Jonmo1]

Sub test()
Range(ActiveCell.Row & ":" & ActiveCell.Row + Range("e5").Value - 1).FillDown
End Sub

 

[HalfAce]

Hello ctc005,
What exactly do you mean by "repeated"?
(Copied?) If so, do you want rows inserted (?), or simply copied to the number of rows
entered in E5 (-1) below the active row?

Assuming you want the active row copied to the row(s) below it - (to total the number in E5),
you don't need to use a loop. You can simply use something like:

Sub Demo()
ActiveCell.EntireRow.Copy _
  Cells(ActiveCell(2).Row, "A").Resize(Range("E5").Value)
End Sub

If I'm on the wrong track then you elaborate a bit?

 

[ctc005]

Two good tips, I'll play with them a bit. My question to you though is, I forgot to mention this, but I have other information below the existing active row and need it to "insert" the rows rather than just fill down. I think that may make a difference. I would just put them all there and leave them blank if they didn't use them, but with a possibility of 150+ rows it would look really bad and be a waste of time to scroll past all the empty rows. So i'm looking for a way for it to be exactly as long as you need it every time. Hope that makes more sense than my previous posts

 

[Jonmo1]

to INSERT

Sub test()
For i = 1 To Range("E5").Value - 1
    Rows(ActiveCell.Row).EntireRow.Copy
    Rows(ActiveCell.Row).EntireRow.Insert
Next i
Application.CutCopyMode = False
End Sub

 

[ctc005]

If I want to specifically use row 10, how can I adjust that so it doesn't do the active cell.

 

[HalfAce]

To specify row 10 you can do something like this. (Again, avoids any looping.)

Sub InsertRowsAndFill()
Cells(11, "A").Resize(Range("E5").Value - 1).EntireRow.Insert
Cells(10, "A").EntireRow.Copy Cells(11, "A").Resize(Range("E5").Value - 1)
End Sub

Hope it helps.