Loop over Multiple Arrays Causing Invalid Qualifier Error

[themefund]

When calling the following Sub I get an Invalid Qualifier Error near the bottom of the code "myEndRecordArray(i).Value = VLookupResult"

Any thoughts?

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

 

[=ODIN=]

What are the values of "i" and "VlookupResult" right before the error is thrown?

 

[=ODIN=]

Also, maybe try removing the .value from these 2 lines:

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

to

[COLOR=#FF0000][B]myEndRecordArray[/B][/COLOR](i) = VLookupResult
myPrintCountArray(i) = LookupResult

 

[themefund]

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:

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:

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