Loop over Multiple Arrays Causing Invalid Qualifier Error

themefund

New Member
Joined
Mar 21, 2019
Messages
2
When calling the following Sub I get an Invalid Qualifier Error near the bottom of the code "myEndRecordArray(i).Value = VLookupResult"

Any thoughts?

Code:
Sub PrintCountCounts()


Dim myTableArray(1 To 6) As String
Dim myEndRecordArray(1 To 6) As String
Dim myPrintCountArray(1 To 6) As String
Dim LookupResult As Long
Dim VLookupResult As String
Dim endRecordValue As String
Dim CountValue As String
Dim i As Integer


myTableArray(1) = Sheets("5 Cut").Range("Table_Page_Count_5cut")
myTableArray(2) = Sheets("8 Cut").Range("Table_Page_Count_8cut")
myTableArray(3) = Sheets("5160 (Manila Folders)").Range("Table_PageCount_5160")
myTableArray(4) = Sheets("5161 (SuperTabs) Stickers").Range("Table_PageCount_5161")
myTableArray(5) = Sheets("5167 (80 Up) Stickers").Range("Table_PageCount_5167")
myTableArray(6) = Sheets("5366 (30 Up Stickers)").Range("Table_PageCount_5366")


myEndRecordArray(1) = Sheets("PrintCounts").Range("EndRecord_5Cut")
myEndRecordArray(2) = Sheets("PrintCounts").Range("EndRecord_8Cut")
myEndRecordArray(3) = Sheets("PrintCounts").Range("EndRecord_5160")
myEndRecordArray(4) = Sheets("PrintCounts").Range("EndRecord_5161")
myEndRecordArray(5) = Sheets("PrintCounts").Range("EndRecord_5167")
myEndRecordArray(6) = Sheets("PrintCounts").Range("EndRecord_5366")


myPrintCountArray(1) = Sheets("PrintCounts").Range("PrintCount_5Cut")
myPrintCountArray(2) = Sheets("PrintCounts").Range("PrintCount_8Cut")
myPrintCountArray(3) = Sheets("PrintCounts").Range("PrintCount_5160")
myPrintCountArray(4) = Sheets("PrintCounts").Range("PrintCount_5161")
myPrintCountArray(5) = Sheets("PrintCounts").Range("PrintCount_5167")
myPrintCountArray(6) = Sheets("PrintCounts").Range("PrintCount_5366")


LookupResult = Cells.Find(What:="*", _
                    After:=Range("A1"), _
                    LookAt:=xlPart, _
                    LookIn:=xlFormulas, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlPrevious, _
                    MatchCase:=False).Row




For i = LBound(myPrintCountArray) To UBound(myPrintCountArray)
VLookupResult = WorksheetFunction.VLookup(LookupResult, myTableArray(i), 2, True)
[COLOR=#FF0000][B]myEndRecordArray[/B][/COLOR](i).Value = VLookupResult
myPrintCountArray(i).Value = LookupResult
Next i


End Sub
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

=ODIN=

Active Member
Joined
Dec 3, 2009
Messages
288
What are the values of "i" and "VlookupResult" right before the error is thrown?
 

=ODIN=

Active Member
Joined
Dec 3, 2009
Messages
288
Also, maybe try removing the .value from these 2 lines:

Code:
[COLOR=#FF0000][B]myEndRecordArray[/B][/COLOR][COLOR=#333333](i).Value = VLookupResult
[/COLOR][COLOR=#333333]myPrintCountArray(i).Value = LookupResult[/COLOR]

to
Code:
[COLOR=#FF0000][B]myEndRecordArray[/B][/COLOR](i) = VLookupResult
myPrintCountArray(i) = LookupResult
 
Last edited:

themefund

New Member
Joined
Mar 21, 2019
Messages
2
I fixed the code myself, I was trying to define simple strings in the array as too complex. I've changed it up so the arrays look like this:

Code:
myTableArray(1) = "Table_Page_Count_5cut"
myTableArray(2) = "Table_Page_Count_8cut"
myTableArray(3) = "Table_PageCount_5160"
myTableArray(4) = "Table_PageCount_5161"
myTableArray(5) = "Table_PageCount_5167"
myTableArray(6) = "Table_PageCount_5366"

And the loop looks like this:

Code:
For i = LBound(myPrintCountArray) To UBound(myPrintCountArray)
VLookupResult = WorksheetFunction.VLookup(LookupResult, Sheets(myTableSheetArray(i)).Range(myTableArray(i)), 2, True)
Sheets("PrintCounts").Range(myEndRecordArray(i)).Value = VLookupResult
If myPrintCountArray(i) = "PrintCount_5Cut" Then
    Sheets("PrintCounts").Range(myPrintCountArray(i)).Value = LookupResult
    ElseIf myTableSheetArray(i) = "8 Cut" Then
        Sheets("PrintCounts").Range(myPrintCountArray(i)).Value = LookupResult
    Else
        Sheets("PrintCounts").Range(myPrintCountArray(i)).Value = VLookupResult
End If


Next i

As you can see I took out the cell references from the arrays and made them static elements in the loop, This means I had to add an additional array to fulfil all the requirements of my code, but it works now :)
 

Watch MrExcel Video

Forum statistics

Threads
1,109,033
Messages
5,526,363
Members
409,697
Latest member
christopherlewis1620

This Week's Hot Topics

Top