issue with arrays with large number of values

Question

I am trying to compare values in arrays after storing fields values from Excel sheet. I am getting error in this line -

If Trim(wsA.Cells(6, 116 + x).Value) = Trim(myArray1(i, 1).Value) Then

(424: Object required)

There are around 80000 values in columns A and D in PO file which I am storing in arrays myArray1 and myArray2.

The complete code is below: Request your help, Thanks

Sub UpdateQtyFrmPO()



StartTime = Timer

'''''''''''''''''''''''''''''''''''''''''''''''''''''''


Allocfile = Application.GetOpenFilename(FileFilter:="Excel Workbook (*.xl*; *.csv), *xl*;*.csv", _
                                        Title:="Select the allocation file ")

If Not (Allocfile <> False) Then

MsgBox "Allocation file not selected. Update Aborted!", vbCritical


Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.EnableEvents = True


Exit Sub

End If


Workbooks.Open Allocfile

Set Alloc = ActiveWorkbook

Set wsA = Alloc.ActiveSheet


'''''''''''''''''''''''''''''''''''''''''''''''''''''''



POfile = Application.GetOpenFilename(FileFilter:="Excel Workbook (*.xl*; *.csv), *xl*;*.csv", _
                                        Title:="Select the PO tracker file ")

If Not (POfile <> False) Then

MsgBox "PO tracker file not selected. Update Aborted!", vbCritical


Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.EnableEvents = True


Exit Sub

End If


Workbooks.Open POfile

Set PO = ActiveWorkbook

Set wsP = PO.ActiveSheet

'''''''''''''
Dim myArray1 As Variant
Dim myArray2 As Variant
Dim myArray3 As Variant

lastrowP = wsP.Range("A1000000").End(xlUp).Row
lastrowA = wsA.Range("A1000000").End(xlUp).Row


wsA.Range("DM7:DT" & lastrowA).ClearContents


''''''''''''''''''''''''''''''''''''''''

myArray1 = wsP.Range("A1:A" & lastrowP).Value
myArray2 = wsP.Range("D1:D" & lastrowP).Value

myArray3 = wsA.Range("A1:A" & lastrowA).Value



  For i = 1 To UBound(myArray1) ''''Job No

  For j = 1 To UBound(myArray2) ''''Article

  For k = 1 To UBound(myArray3) ''''ItemNo


    If myArray3(k, 1) = myArray2(j, 1) Then


      For x = 1 To 8
      wsA.Activate
      If Trim(wsA.Cells(6, 116 + x).Value) = Trim(myArray1(i, 1).Value) Then

      Cells(k, 116 + x).Select

      wsA.Cells(k, 116 + x).Value = Range("O" & i).Value

      End If

      Exit For

      Next x

    End If

    Next k

    Next j

  Next i

@jamheadart Thanks for comment. It is throwing same error with around 9000 values. — , Jun 03 '20 at 11:48
If that is your complete code then you are not using `Option Explicit`. On the other hand, if you are using `Option Explicit` then this isn't your complete code and you are using global variables. Either way, you are not following good VBA practices. — John Coleman, Jun 03 '20 at 11:48
It should throw the same error with 2 values. Size has nothing to do with it. @SJR points out the core problem. — John Coleman, Jun 03 '20 at 11:49
The reason I asked is because your title made it seem like you'd had it working for small arrays, but like others have pointed out, it won't work even with just a few values. Try changing `Trim(myArray1(i, 1).Value)` to `Trim(myArray1(i, 1))` — jamheadart, Jun 03 '20 at 12:10
I highly recommend to indent and format your code properly. Your code gets much easier to maintain. — Pᴇʜ, Jun 03 '20 at 12:16
I have seen use of .value with array variant in another post in stackoverflow. Could you guys please have a look what am I missing here. https://stackoverflow.com/questions/48322557/fastest-vlookup-in-worksheet-data-performed-in-vba — , Jun 03 '20 at 13:01
@shrinivasiyer The post you linked does not use `array.value` and arrays do not have a `.Value` that is a fact. I think you are mixing something up. Check my answer. — Pᴇʜ, Jun 03 '20 at 13:04

score 0 · Accepted Answer · answered Jun 03 '20 at 12:58

Make sure you use Option Explicit and declare all your variables properly
Check your line Exit For this will exit the x loop on every x = 1 already.
Arrays don't have a .Value so myArray1(i, 1).Value does not exist and it must be myArray1(i, 1).
Indent your code properly.
Make sure you don't use ActiveWorkbook if you don't have to (check the code below how do do it properly).
A1000000 is not the last cell in column A. Never hardcode these numbers if you can read them wsP.Rows.Count gives you the max number of rows.
And therefore wsA.Cells(wsA.Rows.Count, "A").End(xlUp).Row is the last used cell in column A.
Make sure all your Range, Cells, Rows, Columns objects have a worksheet/workbook specified. Otherwise you might get unwanted results because Excel is picking the wrong worksheet.

I could not check the behavior of your code but the following is at least fixing the obvious issues. Check it out.

Option Explicit

Public Sub UpdateQtyFrmPO()
    Dim StartTime As Single
    StartTime = Timer

    Dim Allocfile As Variant
    Allocfile = Application.GetOpenFilename(FileFilter:="Excel Workbook (*.xl*; *.csv), *xl*;*.csv", _
                                        Title:="Select the allocation file ")

    If VarType(Allocfile) = vbBoolean And Allocfile = False Then
        MsgBox "Allocation file not selected. Update Aborted!", vbCritical
        Application.DisplayAlerts = True
        Application.ScreenUpdating = True
        Application.EnableEvents = True
        Exit Sub
    End If

    Dim Alloc As Workbook
    Set Alloc = Application.Workbooks.Open(Filename:=Allocfile)

    Dim wsA As Worksheet
    Set wsA = Alloc.ActiveSheet

    Dim POfile As Variant
    POfile = Application.GetOpenFilename(FileFilter:="Excel Workbook (*.xl*; *.csv), *xl*;*.csv", _
                                        Title:="Select the PO tracker file ")

    If VarType(POfile) = vbBoolean And POfile = False Then
        MsgBox "PO tracker file not selected. Update Aborted!", vbCritical
        Application.DisplayAlerts = True
        Application.ScreenUpdating = True
        Application.EnableEvents = True
        Exit Sub
    End If

    Dim PO As Workbook
    Set PO = Workbooks.Open(Filename:=POfile)

    Dim wsP As Worksheet
    Set wsP = PO.ActiveSheet



    Dim myArray1 As Variant
    Dim myArray2 As Variant
    Dim myArray3 As Variant

    Dim LastRowP As Long
    LastRowP = wsP.Cells(wsP.Rows.Count, "A").End(xlUp).Row
    Dim LastRowA As Long
    LastRowA = wsA.Cells(wsA.Rows.Count, "A").End(xlUp).Row


    wsA.Range("DM7:DT" & LastRowA).ClearContents


    myArray1 = wsP.Range("A1:A" & LastRowP).Value
    myArray2 = wsP.Range("D1:D" & LastRowP).Value
    myArray3 = wsA.Range("A1:A" & LastRowA).Value

    Dim i As Long
    For i = 1 To UBound(myArray1) ''''Job No
        Dim j As Long
        For j = 1 To UBound(myArray2) ''''Article
            Dim k As Long
            For k = 1 To UBound(myArray3) ''''ItemNo

                If myArray3(k, 1) = myArray2(j, 1) Then
                    Dim x As Long
                    For x = 1 To 8
                        'wsA.Activate
                        If Trim$(wsA.Cells(6, 116 + x).Value) = Trim$(myArray1(i, 1)) Then
                            'wsA.Cells(k, 116 + x).Select
                            wsA.Cells(k, 116 + x).Value = wsA.Range("O" & i).Value
                        End If

                        Exit For 'this will exit always on x=1 and should probably move up right before End If
                    Next x
                End If
            Next k
        Next j
    Next i
End Sub

Note that you have 4 nested loops. If there are many rows in your worksheets then nested rows can easily run for a very long time.

issue with arrays with large number of values

1 Answers1