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

 

[Fluff]

Try it like

data_val(y) = wsExport.Cells(x, y + csvdata_start).Value

 

[R2ah1ze1l]

Try it like

data_val(y) = wsExport.Cells(x, y + csvdata_start).Value

Run-time error '9'
Subscript out of range

 

[Fluff]

What are wsExport & csvdata_start?

 

[R2ah1ze1l]

fl_split = wsReview.Range("D2").Value
iss_num = Format(wsReview.Range("E2").Value, "Standard")
pn_v = Split(fl_split, "_")(0) 'part number
pt_v = Split(fl_split, "_")(1) 'prog type
po_v = Split(fl_split, "_")(2) 'OP number
pm_v = Split(fl_split, "_")(3) 'Mach ID

efil_nam = fl_split & "-" & iss_num 'file and sheet title

Epath = "M:\Reports Archive\" & pn_v & "\" & "OP" & po_v & "\Export" 'Export folder location (2020 Template)
sExport = efil_nam & ".csv"                                          'CSV file name
FEpath = Epath & "\" & sExport

Set wbExport = Workbooks.Open(FEpath)
Set wsExport = wbExport.Worksheets(efil_nam)
csvdata_start = 3

I use the start location in more than one location and alternate worksheets can start at column 3 up to 9 before the data come out.

 

[Fluff]

Ok, those should be fine, how have you defined & sized data_val

 

[R2ah1ze1l]

Dim data_val() As Long
Then
ReDim data_val(1 To lastr) As Long

I found 1 error that I should be using lastc not lastr.

When I changed that:

        While y < lastc
        y = y + 1
        data_val(y) = wsReview.Cells(ir + y, ic + x).Value = Format(data_val, "0.0000")
        Wend

This is where I'm writing it back now throws a '13' for type mismatch.

 

[Fluff]

Ok, what is the value of lastr when you redim the array & what is the value of y when you get the error?

 

[R2ah1ze1l]

lastr in the file I'm using results in a total of 109 rows
It appeared to trip on #1 because I didn't have Format(data_val ( y ),"0.0000")

However, now that I 'corrected' that, I do not see any data on my wsReview sheet when I execute the code. Though I do not receive any more errors.

 

[Fluff]

There is nothing in the code you posted that puts the array onto a sheet.