Updating Multiple rows with Userform

Question

I am working on a project. I use the following codes to enter Customer's Orders into the Sales Table on Excel through a Userform;

lRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    With ws
        .Cells(lRow, 1).Value = Me.Data1.Value
        .Cells(lRow, 2).Value = Me.Data2.Value
        .Cells(lRow, 3).Value = Me.Data3.Value
        .Cells(lRow, 4).Value = Me.Data4.Value
        .Cells(lRow, 5).Value = Me.Data5.Value
        .Cells(lRow, 6).Value = Me.Data6.Value
        .Cells(lRow, 7).Value = Me.Data7.Value
        .Cells(lRow, 8).Value = Me.Data8.Value
        .Cells(lRow, 9).Value = Me.Data9.Value
        .Cells(lRow, 10).Value = Me.Data10.Value
    End With

The Code above can only update one record in a row at a time. I can only enter one Product per Customer which is entered in a row. I want to be able to enter various products for one customer and post to different rows. What do I do?

Maybe this is what you are looking for: myexcelonline.com/blog/create-data-entry-form-excel (Excel Data Entry Form) — Ralph
– Ralph, Commented Nov 10, 2017 at 14:14
Hello Raph. I have checked it out but that is not what I want. I am using a VBA USERFORM and I want to be able to Select one data into multiple rows on the VBA User Form — UBeny
– UBeny, Commented Nov 10, 2017 at 15:10

Ralph · Accepted Answer · 2017-11-10 15:47:35Z

1

If I understand you correctly you just want to copy the same data set multiple times on the sheet. If that's the case then you can copy the data set like so:

ws.Range(ws.Cells(lRow, 1), ws.Cells(lRow, 10)).Copy ws.Range(ws.Cells(lRow + x_copies, 1), ws.Cells(lRow, 10))

In your code that would be:

lRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Offset(1, 0).Row
With ws
    .Cells(lRow, 1).Value = Me.Data1.Value
    .Cells(lRow, 2).Value = Me.Data2.Value
    .Cells(lRow, 3).Value = Me.Data3.Value
    .Cells(lRow, 4).Value = Me.Data4.Value
    .Cells(lRow, 5).Value = Me.Data5.Value
    .Cells(lRow, 6).Value = Me.Data6.Value
    .Cells(lRow, 7).Value = Me.Data7.Value
    .Cells(lRow, 8).Value = Me.Data8.Value
    .Cells(lRow, 9).Value = Me.Data9.Value
    .Cells(lRow, 10).Value = Me.Data10.Value
    ws.Range(ws.Cells(lRow, 1), ws.Cells(lRow, 10)).Copy ws.Range(ws.Cells(lRow + x_copies, 1), ws.Cells(lRow, 10))
End With

OR you could elect to directly write the values multiple times on the sheet like so:

lRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Offset(1, 0).Row
With ws
    .Cells(lRow, 1).Resize(x_copies).Value = Me.Data2.Value
    .Cells(lRow, 2).Resize(x_copies).Value = Me.Data2.Value
    .Cells(lRow, 3).Resize(x_copies).Value = Me.Data3.Value
    .Cells(lRow, 4).Resize(x_copies).Value = Me.Data4.Value
    .Cells(lRow, 5).Resize(x_copies).Value = Me.Data5.Value
    .Cells(lRow, 6).Resize(x_copies).Value = Me.Data6.Value
    .Cells(lRow, 7).Resize(x_copies).Value = Me.Data7.Value
    .Cells(lRow, 8).Resize(x_copies).Value = Me.Data8.Value
    .Cells(lRow, 9).Resize(x_copies).Value = Me.Data9.Value
    .Cells(lRow, 10).Resize(x_copies).Value = Me.Data10.Value
End With

answered Nov 10, 2017 at 15:47

Ralph

9,4544 gold badges35 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

UBeny Over a year ago

Thanks Raph. What I really want is to be able to select different items form a Userform and update an Excel table. I am not updating the rows with the same data. For example. I am placing Orders for a Customer. I want to be able to select the Customer and the various items that the customer wants to order. A Customer may Order Phones, Laptops, Tablets at the same time. I want the Phone, Qty Ordered and cost to be on one row, then Laptop the next row and then Tablet the next row all with the Customer's Name. With my formular I have to place the order one after the order. I want them done once

Ralph Over a year ago

Then you'll have to expand upon your form to allow for such actions / orders / processes.

Collectives™ on Stack Overflow

Updating Multiple rows with Userform

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related