Need help with repeating a cell or UserForm Input field

[berenger]

I have on my userform a QTY field that I want to use as a how many times to repeat or copy and paste into my excel spreadsheet x amount of times the information from other field on this userform.

Also is there a way to make a Yes Checkbox and a No Checkbox to either show or not show data depending upon what was checked?

 

[berenger]

I am using the following code to copy data in column "C" based on a value in Column "Q" but I continue to get an error and I can't figure out why it won't copy whats in Column "C" x amount of times based on the value in "Q" I want this value to be pasted in Column "C"

For Each cell In Range(Cells(2, "C"), Cells(Cells(Rows.Count, "C").End(xlUp).Row, "C"))
     cpyRng = Cells(Rows.Count, "Q").End(xlUp).Row + 1
     Cells(cpyRng, "C").Resize(cell.Offset(0, 2)) = cell.Value
   Next cell

 

[Rick Rothstein]

     Cells(cpyRng, "C").Resize(cell.Offset(0, 2)) = cell.Value

I'm afraid I didn't understand exactly what you are attempting to do, but I did spot a syntax error. Does changing the above line to this work?

Cells(cpyRng, "C").Resize(1, 2) = Cell.Value

 

[berenger]

I have a userform that has a QTY field on it where a user will enter a QTY and the next field is the item that they want to place on there. So if a user place a 5 it will repeat that same item in Cell C on the next row 5 times.

Your fix to the code did get rid of my error but the code i have entered in there does not work as it is not copying the cell and pasting in the next 5 empty rows below it.

 

[Rick Rothstein]

The problem I am having in following your description is this... the code you posted appears to be trying to do something to each cell in Column C between Rows 2 and the last used row in Column C; however your latest description makes it sound like maybe you want to search for single item text in Column C and once that item is found, add rows under it and repeat the item text in those newly added cells in Column C. Can you clarify which it is? Perhaps a before and after mini-example of sample data and the UserForm fields would help clarify things. Oh and also... are we just repeating Column C's text or the entire row's cell values?

 

[berenger]

Here is what my userform looks like. This might help you with understanding what I am trying to do.


-- removed inline image ---



Here is the code that I have based off of the user hitting the NEXT Button.

Private Sub CommandButton1_Click()
Dim i As Integer
Dim ws As Worksheet
Sheet4.Activate
'Set Ws = Sheets("Sheet4")

    If Not IsEmpty(Sheets("Scan Here").Range("A640")) Then
        MsgBox "You have reached the end of the form."
        Exit Sub
    End If
    
    If IsEmpty(Description) Or Description = "" Then
        MsgBox "No such product found. Please enter a valid UPC or Item #."
        Exit Sub
    End If

    'position cursor in the correct cell A2.
    Range("A2").Select
    i = 1 'set as the first ID
    
    'check to see the next available blank row start at cell A2...
    Do Until ActiveCell.Value = Empty
        ActiveCell.Offset(1, 0).Select 'move down 1 row
        i = i + 1 'keep a count of the ID for later use
            
'Copy Item Number in N Rows            
   For Each Cell In Range(Cells(2, "C"), Cells(Cells(Rows.Count, "C").End(xlUp).Row, "C"))
     cpyRng = Cells(Rows.Count, "C").End(xlUp).Row + 1
     Cells(cpyRng, "C").Resize(1, 2) = Cell.Value
   Next Cell
            
    Loop

    'Populate the new data values into the 'Scan Here' worksheet.
    ActiveCell.Value = i 'Next ID number
    ActiveCell.Offset(0, 1).Value = Me.UPCNum.Value 'set col B
    ActiveCell.Offset(0, 2).Value = Me.ItemNum.Value 'set col C
    ActiveCell.Offset(0, 16).Value = Me.LabelQTY.Value 'set col Q
    
    'Clear down the values ready for the next record entry...
    Me.UPCNum.Value = Empty
    Me.ItemNum.Value = Empty
    Me.Description.Text = Empty
    Me.Quantity.Value = Empty
    Me.Price.Value = Empty
    Me.LabelQTY.Value = Empty
    Me.ItemNum.SetFocus 'positions the cursor for next record entry
    
   LabelCnt.Value = Range("A" & Rows.Count).End(xlUp).Value
   LastItem.Value = Range("C" & Rows.Count).End(xlUp).Value
  
End Sub