Values in a array are disappearing

[Rich]

I'm using an array to save the total quantities of part numbers in various
sequences of a project. The code is shown below.

"a" = the shipping sequence number
aryTotK(a) = the total pieces of part "K" in sequence "a", etc.

If I have only one sheet of part numbers, the code works. If I copy that
sheet, leaving all data the same, my total quantities should be twice
compared to when I only have one sheet of parts. However, when I have more
than one sheet of parts, only the first "sequence" has the correct number of
parts (the total pieces of all part lists), the rest of the sequences only
contain the quantity on the last page of the part lists. When I step thru
the code, I notice that, on part lists after the first one, when "a" is
greater than 1, the "aryTotK(a)" value is a "subscript out of range" until it
after the "redim" command.

Why does it only keep the values from the previous part lists when a=1??

**********************************
a = 1
ReDim aryTotK(a)
ReDim aryTotLH(a)
ReDim aryTotG(a)
ReDim aryTotJS(a)
ReDim aryTotHDR(a)
ReDim aryTotBR(a)
For Each wks In ThisWorkbook.Worksheets
If UCase(wks.Name) Like "S (*)" Then
'***count total quantities of marks
If wks.Range("R1").Value = "M" Then
For a = 1 To iSeqCt
ReDim Preserve aryTotK(a)
ReDim Preserve aryTotLH(a)
ReDim Preserve aryTotG(a)
ReDim Preserve aryTotJS(a)
ReDim Preserve aryTotHDR(a)
ReDim Preserve aryTotBR(a)
For iCol = 2 To 15
If wks.Range(Chr(64 + iCol) + Replace(Str(13), " ", "")).Value =
arySeqList(a) Then
For iRow = 14 To 49
c = ""
For b = 1 To iMkCt - 1
If wks.Range("A" + Replace(Str(iRow), " ", "")).Value = aryJstMk(b)
Then
c = aryMemType(b)
Exit For
End If
Next b
Select Case c
Case "K"
aryTotK(a) = aryTotK(a) + wks.Range(Chr(64 + iCol) +
Replace(Str(iRow), " ", "")).Value
Case "LH"
aryTotLH(a) = aryTotLH(a) + wks.Range(Chr(64 + iCol) +
Replace(Str(iRow), " ", "")).Value
Case "G"
aryTotG(a) = aryTotG(a) + wks.Range(Chr(64 + iCol) +
Replace(Str(iRow), " ", "")).Value
Case "JS"
aryTotJS(a) = aryTotJS(a) + wks.Range(Chr(64 + iCol) +
Replace(Str(iRow), " ", "")).Value
Case "HDR"
aryTotHDR(a) = aryTotHDR(a) + wks.Range(Chr(64 + iCol) +
Replace(Str(iRow), " ", "")).Value
Case "BR"
aryTotBR(a) = aryTotBR(a) + wks.Range(Chr(64 + iCol) +
Replace(Str(iRow), " ", "")).Value
End Select
Next iRow
End If
Next iCol
Next a
End If
*****************************

 

[Dave Peterson]

First, I'm not sure I understand what you're doing, but you have a few things
that could make your code easier to understand.

Instead of using:
wks.Range(Chr(64 + iCol) + Replace(Str(iRow), " ", "")
you can use
wks.cells(irow,icol)
(and when you're concatenating strings, you should use & (not +).)

And when you redim those arrays, you're losing the values in those higher
elements.

For example, if I had an array named myArr and it was dimmed as:
Dim myArr() As Long

Then I could make it 3 elements:
ReDim myArr(1 To 3)

And put some data in it:
myArr(1) = 123
myArr(2) = 456
myArr(3) = 789

But then redim it (and chop some elements):
ReDim Preserve myArr(1 To 1)

I end up with just:
myarr(1) = 123

The other 2 elements are gone.

(and when you use:
reDim myArr(3)
it's the same as
redim myarr(0 to 3)
It's 4 elements -- not 3!)

And I'd do my best not to write code like:

for i = 1 to somevar
when I'm looping through the elements of an array.

I think it's much safer to use:
for i = lbound(somearray) to ubound(somearray)

And I know you've worked with your code enough to be familiar with it. But
variables named a, b, c may not mean much when you come back to fix a bug in a
week or a month or ...

So I changed somethings around, changed some variable names and guessed at
others.

I wouldn't trust it at all, but maybe it'll get you closer:

Option Explicit
Sub testme01()

Dim SeqCtr As Long
Dim wks As Worksheet
Dim iCol As Long
Dim iRow As Long
Dim arySeqList As Variant
Dim MatchingSeqType As String
Dim JstMkCtr As Long
Dim aryJstMk As Variant
Dim aryMemType As Variant

'some way you determined how big arySeqList is
'I'm guessing that each of the totals should be the same size
arySeqList = Array("xxx1", "xxx2", "yyy3")

'and somehow you initialize this aryJstMk to something.
aryJstMk = Array(1, 5, 12, 13, 33)

ReDim aryTotK(LBound(arySeqList) To UBound(arySeqList))
ReDim aryTotLH(LBound(arySeqList) To UBound(arySeqList))
ReDim aryTotG(LBound(arySeqList) To UBound(arySeqList))
ReDim aryTotJS(LBound(arySeqList) To UBound(arySeqList))
ReDim aryTotHDR(LBound(arySeqList) To UBound(arySeqList))
ReDim aryTotBR(LBound(arySeqList) To UBound(arySeqList))


For Each wks In ThisWorkbook.Worksheets
If UCase(wks.Name) Like "S (*)" Then
'***count total quantities of marks
If wks.Range("R1").Value = "M" Then
For SeqCtr = LBound(arySeqList) To UBound(arySeqList)
For iCol = 2 To 15
If wks.Cells(13, iCol).Value = arySeqList(SeqCtr) Then
For iRow = 14 To 49
MatchingSeqType = ""
'why 1 To iMkCt - 1??????
For JstMkCtr = LBound(aryJstMk) To UBound(aryJstMk)
If wks.Cells(iRow, "SeqCtr").Value = aryJstMk(JstMkCtr) Then
MatchingSeqType = aryMemType(JstMkCtr)
Exit For
End If
Next JstMkCtr
Select Case MatchingSeqType
Case "K"
aryTotK(SeqCtr) = aryTotK(SeqCtr) + wks.Cells(iRow, iCol).Value
Case "LH"
aryTotLH(SeqCtr) = aryTotLH(SeqCtr) + wks.Cells(iRow, iCol).Value
Case "G"
aryTotG(SeqCtr) = aryTotG(SeqCtr) + wks.Cells(iRow, iCol).Value
Case "JS"
aryTotJS(SeqCtr) = aryTotJS(SeqCtr) + wks.Cells(iRow, iCol).Value
Case "HDR"
aryTotHDR(SeqCtr) = aryTotHDR(SeqCtr) + wks.Cells(iRow, iCol).Value
Case "BR"
aryTotBR(SeqCtr) = aryTotBR(SeqCtr) + wks.Cells(iRow, iCol).Value
End Select
Next iRow
End If
Next iCol
Next SeqCtr
End If
End If
Next wks
End Sub

 

[Rich]

OK, I see where my problem is....when I "redim preserve aryTotK(1)", I'm
losing the values I previously stored in 2-??. That makes sense
now...apparently the "preserve" is not working the way I thought it would.

I appreciate your other suggestions and will investigate them more to learn
more about them and implement them in my code. I'll need to learn more about
the upper and lower bounds, especially, as I use arrays quite a bit in other
aspects of my project.

I understand your suggestion regarding my use of a, b, c as variables. I
use them strictly as a temporary counter for looping statements. Looks like
I can eliminate them when I learn more about upper and lower bounds.

Unfortunately, I'm just a self-taught "wanna-be programmer" (haha). Most of
my approaches center around trial & error and taking examples from others
(like you), playing with them to try to figure out the ins&outs and creating
what is probably a patchwork of code that may not be the most efficient
solution.

Thanks for your assistance!