VBA writing multiple array values to multiple columns

Question

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

You might want to post a small screenshot of your data - I don't think I understand what you're asking. — SJR
– SJR, Commented Mar 17, 2022 at 10:10
I can't post data, so have tried to include an example: two arrays, same unique ID linking them but columns in different order and may have different data — Toby
– Toby, Commented Mar 17, 2022 at 10:40
Your question is unclear. Do you want just to copy arrays into columns like your title says? Do you want to compare 2 columns of 2 different arrays? Are you trying to merge those arrays into one? Are you trying to get what ID are not common between both arrays? Or do you want only the common ones? — Foxfire And Burns And Burns
– Foxfire And Burns And Burns, Commented Mar 17, 2022 at 10:52
Sorry, it appears my question is not as clear as I intended. I am already comparing the columns/arrays in earlier code but writing back to the worksheets when conditions are met means I have to write each cell in a row individually from the array as the column orders are not comparable between the two arrays. Is there a better way of writing back to the sheets than the line by line instructions posted in the original question? — Toby
– Toby, Commented Mar 17, 2022 at 11:04

Gove · Accepted Answer · 2022-03-17 12:26:02Z

1

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)

answered Mar 17, 2022 at 12:26

Gove

1,82411 silver badges12 bronze badges

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

2 Comments

Toby Over a year ago

Thank you for this, I will try this method. I hadn't thought about using more arrays to map the source and destination!

Gove Over a year ago

@Toby, Glad to be of service. If this does the trick, I'll appreciate an upvote and marking this answer as correct

Collectives™ on Stack Overflow

VBA writing multiple array values to multiple columns

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