Loop Question

[ledl20483037]

Good Evening all! VBA noobie here with a quick question. I have an excel sheet that has built in formulas that I use to populate results in a table. I "recorded" my process but I am trying to create a loop. The below is the current code from the recording. I am needing this loop to run until it has reached the number I put in cell c2 (I am pulling values from column J, running them in the formula, then populating the corresponding row in column L). Thanks so much in advance!

Sub Macro1()
'
' Macro1 Macro
'

'
Range("J9").Select
Selection.Copy
Range("B5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("F11").Select
Application.CutCopyMode = False
Selection.Copy
Range("L9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("J10").Select
Application.CutCopyMode = False
Selection.Copy
Range("B5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("F11").Select
Application.CutCopyMode = False
Selection.Copy
Range("L10").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("J11").Select
Application.CutCopyMode = False
Selection.Copy
Range("B5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("F11").Select
Application.CutCopyMode = False
Selection.Copy
Range("L11").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("J12").Select
Application.CutCopyMode = False
Selection.Copy
Range("B5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("F11").Select
Application.CutCopyMode = False
Selection.Copy
Range("L12").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("J13").Select
Application.CutCopyMode = False
Selection.Copy
Range("B5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("F11").Select
Application.CutCopyMode = False
Selection.Copy
Range("L13").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

 

[DanteAmor]

How about:
If you write a 5 in cell C2 the loop runs 5 times.

Sub Macro2()
  Dim i As Long
  For i = 1 To Range("C2").Value
    Range("B5").Value = Range("J" & i + 8).Value
    Range("L" & i + 8).Value = Range("F11").Value
  Next
End Sub

 

[ledl20483037]

Thanks!!

 

[ledl20483037]

I've hit another snag.

I need to fill in columns to the right of D & E for each row. The number of columns used will be the number in column E. The sum of the numbers in those new columns for each row will be the number in column D. The catch is that I need to distribute the numbers as evenly as possible, meaning can’t just divide column D by column E and then put the remainder in the last column. It’s more like counting out one to each column until the total in column D is reached. For example, in row 3, there would be 6 columns used, F through K. The values in those cells F3 through K3 would be 6, 6, 6, 6, 5, 5. Thanks in advance!

 

[DanteAmor]

That is a different topic than this thread. Create a new thread for each topic.