VBA Array problems

[jack3d]

I can't understand what is wrong with the below program. It executes but returns values of (497) for all variables in the array. Help is appreciated.


Sub Search_PC_Num()

Dim SCRAP(4, 1 To 12) As Double

Do While (Selection.Value <> "")

For m = 1 To 12

VCELL = ActiveCell.Value
ActiveCell.Offset(0, 1).Select
MCELL = Int(Month(ActiveCell.Value))

If VCELL = "STR" Then
ActiveCell.Offset(0, 17).Select
x = ActiveCell.Value
SCRAP(0, m) = SCRAP(0, m) + x
ActiveCell.Offset(1, -18).Select
End If

If VCELL = "GRV" Then
ActiveCell.Offset(0, 17).Select
x = ActiveCell.Value
SCRAP(1, m) = SCRAP(1, m) + x
ActiveCell.Offset(1, -18).Select
End If

If VCELL = "s791" Then
ActiveCell.Offset(0, 17).Select
x = ActiveCell.Value
SCRAP(2, m) = SCRAP(2, m) + x
ActiveCell.Offset(1, -18).Select
End If

If VCELL = "PLX" Then
ActiveCell.Offset(0, 17).Select
x = ActiveCell.Value
SCRAP(3, m) = SCRAP(3, m) + x
ActiveCell.Offset(1, -18).Select
End If

If VCELL = "VICPR" Then
ActiveCell.Offset(0, 17).Select
x = ActiveCell.Value
SCRAP(4, m) = SCRAP(4, m) + x
ActiveCell.Offset(1, -18).Select
End If



Next m

Loop

Sheets("Scrap Rates").Select
Range("D30:O34").FormulaArray = SCRAP(4, 11)



End Sub

 

[Rick Rothstein]

It executes but returns values of (497) for all variables in the array.

Range("D30:O34").FormulaArray = SCRAP(4, 11)

The answer to your question is, I think, that in the above quoted line of code, SCRAP(4, 11) is a single element inside the SCRAP array... it is not the whole array, so you are assign whatever is in that element to all the cell in D30:O34.

I'm not entirely sure what you are trying to do here... you dimension the SCRAP array's first element from 0 to 4, but you only assign values to for this dimension to elements 0 and 1 (unless you have more code that you didn't show us)... why are you assigning an array of constants to a FormulaArray property?... the second element of SCRAP is dimensioned from 1 to 12, but the range you are assigning it to (Columns D:O) is 13 columns across.

 

[jack3d]

The answer to your question is, I think, that in the above quoted line of code, SCRAP(4, 11) is a single element inside the SCRAP array... it is not the whole array, so you are assign whatever is in that element to all the cell in D30:O34.

I'm not entirely sure what you are trying to do here... you dimension the SCRAP array's first element from 0 to 4, but you only assign values to for this dimension to elements 0 and 1 (unless you have more code that you didn't show us)... why are you assigning an array of constants to a FormulaArray property?... the second element of SCRAP is dimensioned from 1 to 12, but the range you are assigning it to (Columns D:O) is 13 columns across.

I am looking for values in column A, "STR" "GRV" etc, and looking for the corresponding value in column S. I am also sorting by month(the 1-12 count for m) and putting it all into a 5x12 array...i think.

Your diagnosis of SCRAP(4,11) being the issue was correct. However, my count is now returning values that are incorrect.

any ideas?