Feb 14 2022 08:38 AM
Hi Guys,
Apologies for the simplistic nature of this request.
I have a list of rows that I need to duplicate 96 times adding in a value from a defined list into columns F2 and G2:
The list has 95 values:
The result would be having 95 copies of the rows ready for bulk loading:
This can be done by hand of course but thought best to see if there was a scripted approach as this will be a task that I will need to repeat going forward.
Thank you SO much in advance.
Rick
Feb 14 2022 09:26 AM
SolutionMaybe with these lines of code. An adjustment to the name of the worksheet containing the 95 values is required. You can click the button in cell I2 to execute the macro.
Sub multiply()
Dim i As Integer
Dim j As Integer
Dim z As Integer
z = 0
For j = 2 To 96
For i = 2 To 96
Cells(i + z, 6).Value = Cells(j, 3).Value
Cells(i + z, 7).Value = Cells(j, 4).Value
Next i
z = z + 95
Next j
End Sub
Feb 15 2022 12:59 AM
This is FANTASTIC!
Thank you so very much!
Just in the process of reverse engineering your script so I can fully appreciate/understand!
Thank you again
Rick
Feb 15 2022 02:17 AM
Apologies for further queries, as a separate script, how would I go about copying a block of rows/columns and replicating this ie:
If I wanted to duplicate the block of rows several times in a sheet.
Thanks in advance.
Feb 15 2022 06:24 AM
Sub multiply()
Dim i As Integer
Dim j As Integer
Dim z As Integer
Dim no_of_rows As Integer
Dim start_row As Integer
Dim no_of_multiplications As Integer
Range("F:G").Clear
start_row = Cells(1, 5).Value
no_of_rows = Cells(2, 5).Value
no_of_multiplications = Cells(3, 5).Value
z = 0
For i = 1 To no_of_multiplications
For j = start_row To no_of_rows + start_row - 1
Cells(i + z, 6).Value = Cells(j, 1).Value
Cells(i + z, 7).Value = Cells(j, 2).Value
z = z + 1
Next j
Next i
End Sub
With this code i can enter the start_row, no_of_rows and no_of_multiplications in cells E1, E2 and E3. After clicking the button in cell I2 the block of data is multiplied in columns F and G.
Feb 16 2022 12:56 AM
Feb 14 2022 09:26 AM
SolutionMaybe with these lines of code. An adjustment to the name of the worksheet containing the 95 values is required. You can click the button in cell I2 to execute the macro.
Sub multiply()
Dim i As Integer
Dim j As Integer
Dim z As Integer
z = 0
For j = 2 To 96
For i = 2 To 96
Cells(i + z, 6).Value = Cells(j, 3).Value
Cells(i + z, 7).Value = Cells(j, 4).Value
Next i
z = z + 95
Next j
End Sub