CODEWITHSUNDEEP
arraysexcelvba
DGuzmanG
DGuzmanG

Reputation: 71

Populate a row on a sheet for each item on a table and several other cells on another sheet

I'm trying to copy data from a data entry/form sheet "SalesEntry" to a "SalesLog" table on a separate sheet "SalesLog". As I have done before with this code (adapted from here):

Private Sub SaleEntry_Click()
    Dim config, itm, arr
    Dim rw As Range, listCols As ListColumns
    Dim shtForm As Worksheet
     
    Set shtForm = Worksheets("SalesEntry") '<< data source
    With Sheets("SalesLog").ListObjects("SalesLog")
            Set rw = .ListRows.Add.Range 'add a new row and get its Range
            Set listCols = .ListColumns  'get the columns collection
    End With
    'array of strings with pairs of "[colname]<>[range address]"
    config = Array("Fecha<>B3", "Client<>E3", "Product<>?", "Quantity<>?", "Total Sale Price<>?", "Tax Charges<>D26", "Customs Charges<>D27", " Shipping Charges<>D28", "Sale Channel<>B5", "Sale Channel ID<>E5", "Payment Channel<>B7", "Payment Status<>B9", "Amount payed<>E9")
    ' loop over each item in the config array and transfer the value to the appropriate column
    For Each itm In config
    arr = Split(itm, "<>") ' split to colname and cell address
    rw.Cells(listCols(arr(0)).Index).Value = shtForm.Range(arr(1)).Value
    Next itm
End Sub

This works great for storing the info from a few original scattered cells in the entry sheet.

However the problem I have is that in the entry sheet I have a table which contains the items sold on each order, and I need to create a row on the table for each of those items while duplicating the info from the fields on the entry sheet that are not on the items table.

Find below a screenshot of the data entry form. In blue is the items table and in red are the scattered values I'd like to paste for each item on the SalesLog table. Data Entry Sheet. Values are only examples

And this is how the resulting table should look: Result Table

I have read several articles, the documentation and some posts here, but I'm not sure about the solution. I really like the code above and how it stores data in an array and populates things easily.

So far I have 3 possible courses of action:

  1. Merge and fill two arrays: Create an array for the scattered fields (above the items table) and create an array for the items table and then merging them using the items array as a secondary dimension and duplicating every other field array item for as many items in this secondary dimension. I know how to create the two arrays, but I'm not sure how to merge them into a bidimensional array and a few attemps I've made have returned an error.

  2. Double For loop: Create a loop that appends the scattered cell values for each item in the items table and then looping that result into the SalesLog table, as per the original code above. To me this is the most feasible solution but I'm not sure about the loop order and I think using a bunch of ReDim Preserve is not the correct way and at the end of some loops I get a 1004 error or nothing happening.

  3. Use a collection?: As far as I have read and since the items will vary with each sale, I've read that collections are more suited; although the size of the array could be simply retrieved by counting rows in the items table before any operation. I have never worked with Collections before and honestly can't tell how to use it.

Can someone point me in the right direction??

I would also like to include a Now () Timestamp for each Row when the user uses the macro(clicks on the "Add sale" button)

Upvotes: 0

Views: 214

Answers (1)

teylyn
teylyn

Reputation: 35935

Don't overthink this.

You want to generate a row for each item, so the easiest approach is to count the items in the table, then run a for loop and for each item in the table copy the data from the table row and the scattered fields.

This is pretty straightforward and you don't need to mess around with arrays or collections. Why make things more complicated than they need to be?

pseudo code:

myCount = Count rows in item table
for i = 1 to myCount
   copy table cell1 in table row i' this is from the table
   copy table cell2 in table row i' this is from the table
   copy cell3 ' this is from somewhere else in the form
...
   copy celln
next i

Upvotes: 2

Related Questions