Expected Array compiler error in excel VBA

Question

This code gives me a compiler error, "Expected Array". Does anybody know why this is? I want my array to be filled with the corresponding values within the rows that fit my given parameters.

A bonus question- Once I figure that out, I want to count the unique # of values that got entered into that array. However, I am mainly concerned with my first error, and I am greatly appreciative of any help or guidance provided.

 Dim dateCheck As String
Dim lastRow As Long
Dim L As Integer
Dim I As Long
Dim shipDay As Date
Dim search As String
Dim myArray As Variant

For L = 0 To 21  ' Execute code for whole month

shipDay = Worksheets("June Canada").Cells(L + 10, 10).Text   'Check specific ship days

    For I = 1 To Worksheets("Sheet1").UsedRange.Rows.Count  ' Check every row of worksheet

    search = Worksheets("Sheet1").Cells(I, 12).Value ' Variable to check for CAN vs USA

    If ((InStr(1, search, "CAN", vbBinaryCompare) = 1) _
            And (Worksheets("Sheet1").Cells(I, 8) = shipDay) _
            And (Worksheets("Sheet1").Cells(I, 6).Text = "Invoice")) Then

             ReDim myArray(lb To ub)
             lb = 0   ' lower bound = 0
             ub = WorWorksheets("Sheet1").UsedRange.Rows.Count ' upper bound is max # of rows

             myArray() = Worksheets("Sheet1").Cells(I, 10).Text ' Add the variable values to the dynamic array

            End If



    Next I

' Count # of unique values in unique ()

' Worksheets("JUNE canada").Cells(L + 10, 8).Value = ???  'Enter # of unique values into sheet

Next L

you have defined myArray as string and you are trying to redim this. Redim only works on an Array — Krishna
– Krishna, Commented Jul 7, 2015 at 20:05

paul bica · Accepted Answer · 2015-07-07 20:38:04Z

To fix the initial array issue change this:

myArray() = Worksheets("Sheet1").Cells(I, 10).Text

to this:

myArray(I) = Worksheets("Sheet1").Cells(I, 10)

I changed to code to demonstrate one way to determine unique values entered in the array:

Option Explicit

Sub yourFunctionNameHere()

    Dim L As Long, I As Long
    Dim LB As Long, UB As Long
    Dim dateCheck As String
    Dim lastRow As Long
    Dim shipDay As Date
    Dim search As String
    Dim myArray As Variant
    Dim uniques As Object

    With Worksheets("Sheet1")
        LB = 0
        UB = .UsedRange.Rows.Count
        ReDim myArray(LB To UB)
        Set uniques = CreateObject("Scripting.Dictionary")
        For L = 0 To 21
            shipDay = Worksheets("June Canada").Cells(L + 10, 10).Value2
            For I = 1 To UB
                search = .Cells(I, 12).Value2
                If ((InStr(1, search, "CAN", vbBinaryCompare) = 1) _
                        And (.Cells(I, 8) = shipDay) _
                        And (.Cells(I, 6).Text = "Invoice")) Then
                    myArray(I) = .Cells(I, 10).Value2
                    If Not uniques.Exists(myArray(I)) Then
                        uniques.Add Key:=myArray(I), Item:=I
                End If
            Next
        Next
        MsgBox "Unique values: " & uniques.Count
    End With
End Sub

Collectives™ on Stack Overflow

Expected Array compiler error in excel VBA

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related