VBA writing multiple array values to multiple columns-CodePudding

I have tried searching for an answer to this question and have come up empty.

I am loading two worksheets into arrays - both worksheets contain the same unique ID but the rest of the ID data can be different - I am then checking the unique IDs against each other, and if matched then comparing and updating the sheets using the array first dimension to reference the worksheet rows.

When I am writing back to the sheet, I am currently using the below code:

Range("A" & StRow).Value = copyArr(newLine, 1)
Range("B" & StRow).Value = copyArr(wipLine, 2)
Range("C" & StRow).Value = copyArr(wipLine, 6)
...
Range("AA" & StRow).Value = copyArr(wipLine, 50)

The data in each array is not in the same location in each (i.e. array1 col 10 might be array2 col 43, etc).

Is there a more efficient way of doing this that I am missing? Could I write the array back to another sheet, move it about so the columns line up and then use that to write back a row at a time? Is this even a problem and I should just move on?

Any input is appreciated.

example

CodePudding user response：

Because the source columns and the destination columns are in different orders, you need some way to map from the source to the destination. Right now you are doing the mapping in the code that writes from the soure to the destination. As a result, you need a line of code for each mapping. However, if you were to do the mapping in another construct (like an array), you could write a loop to do the output. Here's an example:

    Dim source As Variant
    Dim dest As Variant
    Dim x As Integer
    
    source = Array(1, 2, 6, 50)
    dest = Array("A", "B", "C", "AA")
    
    For x = LBound(soruce) To UBound(source)
        Range(dest(x)   StRow).Value = copyArr(wipLine, source(x))
    Next

The first time through the loop, x will be 0 so dest(x) will be "A" and source(x) will be 1. Therefore:

Range(dest(x)   StRow).Value = copyArr(wipLine, source(x))

will be equivalent to:

Range("A" & StRow).Value = copyArr(newLine, 1)

The second time through the loop, x will be 1 so dest(x) will be "B" and source(x) will be 2. Therefore:

Range(dest(x)   StRow).Value = copyArr(wipLine, source(x))

will be equivalent to:

Range("B" & StRow).Value = copyArr(newLine, 2)