VBA: creating array

[R2ah1ze1l]

In the loops below, I am attempting to copy a series of data that is along a row, then I want to paste it into another workbook/sheet transposed into a column. When I get to " data_val = wsExport.Range(Cells(x, y + csvdata_start)).Value" line I am receiving a 1004 Runtime error: Method 'Range' of object'_Worksheet' failed.

Adding the workbook gets me a 438 error
Removing the "wsExport." gets me the same 1004 error.

I had the data transferring with the 2 while loops but the method I used was VERY slow and I was hoping this array might help pick up the pace...

While x < lastr
x = x + 1                                                               'begins rows
wsExport.Activate
        sn_val = wsExport.Cells(x, 1).Value
        oper_val = wsExport.Cells(x, 2).Value
        dt_val = wsExport.Cells(x, 3).Value
        'tm_val = wsExport.Cells(x, 4).Value
        While y < lastc                                                 'begin column
        y = y + 1
        data_val(y) = wsExport.Range(Cells(x, y + csvdata_start)).Value
        Wend                                                            'end column
        y = 0
wsReview.Activate
        wsReview.Cells(2, ic + x).Value = sn_val
        wsReview.Cells(3, ic + x).Value = oper_val
        wsReview.Cells(4, ic + x).Value = dt_val
        'wsReview.Cells(5, ic + x).Value = tm_val
        While y < lastc
        y = y + 1
        data_val(y) = Range(Cells(ir + y, ic + x)).Value = Format(data_val, "0.0000")
        Wend
        y = 0
Wend

 

[R2ah1ze1l]

While x < lastr
x = x + 1                                                               'begins rows
wsExport.Activate
        sn_val = wsExport.Cells(x, 1).Value
        oper_val = wsExport.Cells(x, 2).Value
        dt_val = wsExport.Cells(x, 3).Value
        'tm_val = wsExport.Cells(x, 4).Value
        While y < lastc                                                 'begin column
        y = y + 1
        data_val(y) = wsExport.Cells(x, y + csvdata_start).Value
        Wend                                                            'end column
        y = 0
wsReview.Activate
        wsReview.Cells(2, ic + x).Value = sn_val
        wsReview.Cells(3, ic + x).Value = oper_val
        wsReview.Cells(4, ic + x).Value = dt_val
        'wsReview.Cells(5, ic + x).Value = tm_val
        While y < lastc
        y = y + 1
        wsReview.Cells(ir + y, ic + x).Value = Format(data_val(y), "0.0000")
        Wend
        y = 0
Wend                                                                     'end rows

Where it calls wsReview, it is writing the data into my main file. wsExport is where the data is collected from.
The format does not appear to like working but that is another subject. I found I did a copy/paste within the code that was not throwing an error but was preventing the data from appearing on the worksheet.

data_val(y) = Range(Cells(ir + y, ic + x)).Value = Format(data_val(y), "0.0000")

Thank you for your help!

 

[Fluff]

Try it like

wsReview.Activate
        wsReview.Cells(2, ic + x).Value = sn_val
        wsReview.Cells(3, ic + x).Value = oper_val
        wsReview.Cells(4, ic + x).Value = dt_val
        'wsReview.Cells(5, ic + x).Value = tm_val
        wsReview.Cells(ir + 1, ic + x).Resize(UBound(data_val), 1).Value = Application.Transpose(data_val)
        y = 0