ASSIGN MULTIPLE VARIABLE FROM SINGLE VALUES IN ARRAY

[vocedicore]

Hi, All

I have 62 columns on destination sheet.
origin sheet has 62 values with different order, need to move to destination sheet with destination's order as below.
<origin sheet>
-B4~B10
-E4~F10
-H4~H10
-E11~E32 and etc.

My idea is as below, but some part is I don't know how to do it, please help
1) read cells from origin sheet using with several array.
2) store each single value from array to 62 different variable. <I can't do this part, please help>
3) 62 variable save to destination sheet with destination's order.

values in array: | 234 | | 08/25/18 | | provided | | 10.235 | ………..62nd

variable in array: | testA | | testC | | prA | | prD | ………..62nd

Destination order: | prA | | testA | | prD | | testC | ………..62nd


Thank you in advance.

[Paul_Hossler]

Post a small workbook with the before and after

One row or many rows?


Entire column?


Do values go from single row to single row?

[vocedicore]

Hi Paul,

orgin sheet has many row with several column.
Deatination has many row with 62 column, need to transpose from origin(single row to single column)
62 blank variable(string, date, single, long) need to receive values from array.
the reason of assign values to variable(not direct to cell), destination have different order.
So, as soon as this variable assigned values(variable has mounted on array as destination order), I will put to destination.
but currently, I don't have way to assign this variable has mounted on array.
Please help.

[Paul_Hossler]

I'm not understanding the example

It's not clear how the origin maps to the destination. I mapped 8 values (in yellow) from origin to destination, but have no idea what else to do with the rest of the data (if anything)

"M" is not the 62nd column on destination, there's only 29 values on origin, looks like there's a lot of groups of 4 cells in D and E that have the same type of data

I think you need a more complete example that contains more data and that uses all of the data on the destination sheet, and which more explicitly shows what goes where

origin.JPG


destination.JPG

[vocedicore]

Hi Paul,

here's more better version, test1,2,3, pr1,2,3, all this sample is not actual head name.
the actual head name is only string(not include number), I just put some names to make easy sample.
Is there way to assign variables in array as below?
the below code is sample, but actual arry has more variable(62 of them).

arry = array(test1,pr1,pr3,pr2,test2,test3)
Cells(2, "B").Resize(, UBound(arry) + 1) = arry

[Paul_Hossler]

I think the array approach (while potentially a tad faster) is way more complicated than just Match-ing and copying data

pr28 and pr29 are not on 'destination'

Option Explicit


Dim rDest As Range
Sub MoveData()
    Dim rTest As Range, rPR As Range, rTB As Range, rQR As Range, rTR As Range, rZT As Range

    Set rDest = Worksheets("destination").Range("B1")
    Set rTest = Worksheets("origin").Range("A4")
    Set rPR = Worksheets("origin").Range("D4")
    Set rTB = Worksheets("origin").Range("F4")
    Set rQR = Worksheets("origin").Range("H4")
    Set rTR = Worksheets("origin").Range("J4")
    Set rZT = Worksheets("origin").Range("L4")


    Set rDest = Range(rDest, rDest.End(xlToRight))
    
    Call pvtCopy(Range(rTest, rTest.End(xlDown)))
    Call pvtCopy(Range(rPR, rPR.End(xlDown)))
    Call pvtCopy(Range(rTB, rTB.End(xlDown)))
    Call pvtCopy(Range(rQR, rQR.End(xlDown)))
    Call pvtCopy(Range(rTR, rTR.End(xlDown)))
    Call pvtCopy(Range(rZT, rZT.End(xlDown)))

End Sub

Private Sub pvtCopy(r As Range)
    Dim rCell As Range
    Dim i As Long
    For Each rCell In r.Cells
        i = 0
        On Error Resume Next
        i = Application.WorksheetFunction.Match(rCell.Value, rDest, 0)
        On Error GoTo 0
        If i <> 0 Then rDest.Cells(1, i).Offset(1, 0).Value = rCell.Offset(0, 1).Value
    Next
End Sub

[vocedicore]

I appreciate it.
Is there way to assign variables mounted in array?

[Paul_Hossler]

?

Option Explicit

Sub test()
    Dim ary1(1 To 4), ary2(1 To 4)
    
    ary1(1) = "AAAA"
    ary1(2) = "BBBB"
    ary1(3) = "CCCC"
    ary1(4) = "DDDD"
    
    ary2(1) = ary1(2)
    ary2(2) = ary1(3)
    ary2(3) = ary1(4)
    ary2(4) = ary1(1)
    
    Range("A1").Value = ary2(3)
    Range("A2").Value = ary2(4)
    Range("A3").Value = ary2(2)
    Range("A4").Value = ary2(1)

End Sub