User Enter Number in Cell on "Form", when user submits, Data from Form copied to another s-CodePudding

I want the user to fill out a "form" in Excel where there is a cell labeled "Number of Strands" and when the user hits the "Submit" button of the form, the data is copied to another sheet named "OSP" and the inputted data is copied the number of times equal to that of "Number of Strands" cell. So if I fill out the form and have Number of Strands = 5, I want that data copied into 5 rows at the bottom of "OSP" sheet. I have attached a screenshot of the form and the code I have so far is below. I have been able to get the form to copy the data from the form to the "OSP" sheet, but cannot figure out how to copy that data x number of times.

I was originally trying to have so if the user had a start and end strand number, say 1-5, it would count the integers in that range (5), insert that number of rows with the inputted data, but also Number the "Strand Number" column of the newly made 5 rows to show descending as 1, 2, 3, 4, 5. If the user were to input a range of 440-460, when submitted, 21 rows would be added and the "Strand Number" column would display 440, 441, 442, 443, etc, but I found this to be too complicated.

I tried multiple code options but can't find one that truly suits my need. Any help copying the data x number of times based on this cell's value would be greatly appreciated.

Pic of the Form

Sub data_input()
   ws_output = "OSP"
   next_row = Sheets(ws_output).Range("A" & Rows.Count).End(xlUp).Offset(1).Row

   Sheets(ws_output).Cells(next_row, 1).Value = Range("C6").Value
   Sheets(ws_output).Cells(next_row, 2).Value = Range("C8").Value
   Sheets(ws_output).Cells(next_row, 3).Value = Range("C10").Value
   Sheets(ws_output).Cells(next_row, 4).Value = Range("C12").Value
   Sheets(ws_output).Cells(next_row, 5).Value = Range("C14").Value
   Sheets(ws_output).Cells(next_row, 6).Value = Range("C16").Value
   Sheets(ws_output).Cells(next_row, 7).Value = Range("C18").Value

   Sheets(ws_output).Cells(next_row, 8).Value = Range("G6").Value
   Sheets(ws_output).Cells(next_row, 9).Value = Range("G8").Value
   Sheets(ws_output).Cells(next_row, 10).Value = Range("G10").Value
   Sheets(ws_output).Cells(next_row, 11).Value = Range("G12").Value
   Sheets(ws_output).Cells(next_row, 12).Value = Range("G14").Value
   Sheets(ws_output).Cells(next_row, 13).Value = Range("G16").Value
   Sheets(ws_output).Cells(next_row, 14).Value = Range("G18").Value

   End Sub

CodePudding user response：

You should insert a loop in your code and then perform the action incrementally (or you could research how to use arrays but that might be a bit advanced for now).

Something like this in your code....

Dim numbOfStrands As Long, x As Long

numbOfStrands = Range("G??").value 'however you can grab the value

For x = 1 To numbOfStrands
 'this action will be run as many times as number of strands.
 'note that the x also works to increase the row number of an entry.
 
    Sheets(ws_output).Cells(next_row   x, ????).Value = Range("????").Value    

Next

CodePudding user response：

One approach:

Dim rngOut As Range, numRows As Long, wsForm As Worksheet, n As Long

Set wsForm = ActiveSheet

numRows = wsForm.Range("C16").Value 'number of strands

With ThisWorkbook.Worksheets("OSP")
    'set the output range
    Set rngOut = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).EntireRow.Resize(numRows)
End With

'fill the various values from the form
rngOut.Columns(1).Value = Range("C6").Value
rngOut.Columns(2).Value = Range("C8").Value
'etc etc

'number the rows in the output
For n = 1 To numRows
    rngOut.Columns(6).Cells(n).Value = n
Next n