Cannot Pass Array Values to Sheet

lforster9999

New Member
Joined
Sep 16, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Hi, I am trying to pass data from an Array to certain sheets, however passing the values to the sheets does not seem to work and results in an error of "Run Time Error 9: Subscript Out of Range"

The data is stored in the array as shown by the screenshot of the local window below. Then when resizing the range to be filled, it errors out. I think the Array may be saved as a 3D array rather than a 2D one (just guessing) .

Code to populate the sheet is as follows.

VBA Code:
Sub InsertArrayToSheet(ArrayName, DestinationSheetName)

Dim ArrLastRow As Long: ArrLastRow = UBound(ArrayName) - LBound(ArrayName)
Dim DestinationRange As Range

'Set DestinationRange = Range("A1:AO" & (ArrLastRow))

Sheets(DestinationSheetName).Range("A1").Resize(UBound(ArrayName, 1) + 1, UBound(ArrayName, 2) + 1).Value = ArrayName

End Sub

The code is being called as follows.

VBA Code:
'Fill PQL, 1ITW, 2ITW, PPL sheet

Call InsertArrayToSheet(PQLArray, "PQL")

Private details have been scribbled out in photo.


Any help is greatly appreciated as it is really annoying me!
1600606442991.png
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

lforster9999

New Member
Joined
Sep 16, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
For reference, the array is being created by this sub
VBA Code:
Sub AddProcessStageToArray(SourceWorksheet, RawDataArray, LastrowData, WhatStage, ArrayOutput)

For i = LBound(RawDataArray) To UBound(RawDataArray)
    If RawDataArray(i, 13) = WhatStage And RawDataArray(i, 38) <> "NOK" Then
        o = o + 1

        'Dim ArrayName() As Variant
        ReDim Preserve ArrayOutput(o)
        ArrayOutput(o) = Application.Index(SourceWorksheet.Range("A1:AO" & LastrowData), i, 0)
        
    End If
Next

End Sub

And being called like this.

Code:
Dim PQLArray() As Variant
Call AddProcessStageToArray(DataWs, DataArr, LastrowData, "Prequalification", PQLArray)
 

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,124
Office Version
  1. 365
Platform
  1. Windows
You're getting the error because AddProcessStageToArray creates a 1-D array but you subsequently refer to Ubound(ArrayName, 2), i.e. as if it were a 2-D array.

You seem unclear whether PQLArray should be 1-D or 2-D?

I'm also curious - does RawDataArray contain the contents of SourceWorksheet.Range("A1:AO" & LastrowData?
 

lforster9999

New Member
Joined
Sep 16, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
PQL array should be a 2D array, as it stretches 41 columns and over a few thousands rows.

And yes, SourceWorksheet.Range("A1:AO" & LastrowData? is the same as RawDataArray, however when using RawDataArray it produced an error. Would you have any advice?
 

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,124
Office Version
  1. 365
Platform
  1. Windows
Here's one way you could code this (shown all within one Sub here, to keep it simple)

VBA Code:
Sub MyCopy()

    Dim rngInput As Range
    Dim vInput As Variant, vOutput() As Variant
    Dim WhatStage As Long, i As Long, j As Long, counter As Long
    
    WhatStage = 1 'say
    
    With Worksheets("Sheet1")
        Set rngInput = .Range("A1:AO" & .Range("A" & .Rows.Count).End(xlUp).Row)
    End With
    vInput = rngInput.Value
    ReDim vOutput(1 To UBound(vInput), 1 To UBound(vInput, 2))
    
    For i = 1 To UBound(vInput)
        If vInput(i, 13) = WhatStage And vInput(i, 38) <> "NOK" Then
            counter = counter + 1
            For j = 1 To UBound(vInput, 2)
                vOutput(counter, j) = vInput(i, j)
            Next j
        End If
    Next i
    
    With Worksheets("Sheet2")
        .Cells.ClearContents
        .Range("A1").Resize(counter, UBound(vInput, 2)).Value = vOutput
    End With

End Sub

INPUT:
.
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAO
1NameStageStatusLastCol
2A1NOKBlah
3B2PendingBlah
4C1PendingBlah
5D3PendingBlah
6E2OKBlah
7G1NOKBlah
8H1OKBlah
9I2PendingBlah
10J1OKBlah
11K4NOKBlah
12L1OKBlah
Sheet1


OUTPUT:
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAO
1C1PendingBlah
2H1OKBlah
3J1OKBlah
4L1OKBlah
Sheet2


But with thousands of rows, it will be much more efficient to filter and copy:

Code:
Sub MyFilter()

    Dim rngInput As Range
    Dim WhatStage As Long
    
    WhatStage = 1 'say
    Worksheets("Sheet2").Cells.ClearContents
    
    With Worksheets("Sheet1")
        Set rngInput = .Range("A1:AO" & .Range("A" & .Rows.Count).End(xlUp).Row)
        With rngInput
            .AutoFilter Field:=13, Criteria1:=WhatStage
            .AutoFilter Field:=38, Criteria1:="<>NOK"
            .Offset(1).Copy Worksheets("Sheet2").Range("A1")
            .AutoFilter
        End With
    End With
    
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,129,527
Messages
5,636,834
Members
416,945
Latest member
Himu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top