How to fix macro not returning value to correct field?

Hello everyone,

I have macro, thanks to sintek and xlnitwit (users of this forum). It works well for the most part, but there is an issue with it when it comes to the tab it creates named "Outgoing Wires". As you see in the highlighted part below, the code should first grab the values in column 9 from sheet1 and place them in column "D" from the "Outgoing Wires" tab. Then it should get the values from column 8 from sheet1 and place them in column "E". For some reason the values are being brought over but backwards and it doesn't matter if you change the value from 8 to 9 the code brings the same value over. Could someone review and advise? Also if you notice, some of the values in column "F" for the "Outgoing Wires" is returned as 6.54316e+16, anyone know why the code doesn't return the same format from sheet1?

Please Login or Register  to view this content.

It's hard to determine what the problem is, I have noticed some of the code is not referring to the correct sheet.
Some areas you need a period to confirm what sheet you are referring to.

Please Login or Register  to view this content.

Columns would also have to be confirmed.

Please Login or Register  to view this content.

The code may be referring to the incorrect sheet if you don't confirm it.

I just added the periods, thanks for that. The code is referring to the correct sheet, the values it brings over to the "Outgoing Wires" tab although some are not in the correct order, they are the values we should see.

That is very interesting, I've added a msgbox to the code and it still comes up with the 8 before the 9. I assume it has something to do with the array, and it wants to do it in order, but other orders do not come up incorrectly.
Just as a test, if you changed the 8 to a 7, it does run in the correct order.

Please Login or Register  to view this content.

I think you are on to something, I replaced the 8 with 7 and it works just like you said but I do need the values from 8 to come up. I added your code and get (without the quotation marks) "$BN$3", "$C$3", "$H$3", "$I$3". "$M$3", "$P$3", "$Q$3" which translates to cell 66, 3, 8, 9, 13, 16, 17. For some reason instead of running

Please Login or Register  to view this content.

it does

Please Login or Register  to view this content.

even though that's not the order set.

When I run your code with replacing 8 with 7 the code brings the right right values.

Basically it's how UNION puts together range, or rather Excel handles range.

If you add Debug.Print rng.Address right after UNION line. You should see address returned as below.
"$BN$3,$C$3,$H$3:$I$3,$M$3,$P$3:$Q$3"

Any contiguous range will be handled as one and will always get put together from left to right (depending on language and Excel's setting it can reverse).

If you want to avoid this, instead of using Union, just put entire used range into array and do all manipulation within and use ARRAY() instead of UNION or just directly access using element index.

Edit: Alternately, you can put cells in to some collection and loop through it, instead of Union range.

It is because they are contiguous, so Union turns them into one area, and then the For Each loops from left to right. You can skip the Union and just create an array though

Please Login or Register  to view this content.

The Value and format of a cell are independent of each other. Assigning the value of one cell to another cell does not transfer formatting.