Problems populating an array

[John N.]

Hello, I have a workbook that has a sheet with a group of employees
(referred to as AM's). Each AM has a record for each day of the
current month. I am separating each AM's records and putting it into
its own sheet. I then summarize each column of data for each AM and
find the average for a few of the columns. This is what the data
looks like on one AM's sheet:

A B C D E F G H .....
1
2
3
4 name
5 queue
6 team
7 site
8 divmgr
9 team type
10 GM
11
12
13 Date Asg Sch Wk RPC PTP ...
14 7/1/04 200 30 15 30 12 10% 15% ...
15 7/2/04 190 25 16 34 13 11% 16% ...
16 7/3/04 200 30 15 30 12 10% 15% ...
17 7/4/04 210 45 17 23 11 9% 15% ...
18 7/5/04 200 30 15 30 12 10% 15% ...
19 7/6/04 200 30 15 30 12 10% 15% ...
20 total: 1029 190 93 177 72 10% 15% ...
21 avg: 171 31 15 29 12
..
..
..


This is working fine. My problem is when I Try and put the totals and
averages into an array. The correct value is not being added to the
array element.

I create a variable to hold the values and define the array.

Public aryAMSummary() As TeamSummary

Public Type TeamSummary
Site As String
DivMng As String
Queue As String
AM As String
Team As Integer
TeamType As String
GMng As String
LstDay As Date
Assigned As Double
Scheduled As Double
Worked As Double
RPC As Double
PTP As Double
SchedP As Double
PenP As Double
ContantP As Double
ConvP As Double
TeamCount As Integer
LineNumber As Integer
End Type

I then separate the AM's and total the columns. After this is done for
each AM. Then I populate the totals/averges into an array to be used
later. This is the code to populate the array.

ReDim Preserve aryAMSummary(intAMCountTmp) 'intAMCountTmp is a
variable the holds the count of AM's

aryAMSummary(intAMCountTmp).AM = Range("B4").Value 'This works

aryAMSummary(intAMCountTmp).Queue = Range("B5").Value 'This works

aryAMSummary(intAMCountTmp).Team = Range("B6").Value 'This works

aryAMSummary(intAMCountTmp).Site = Range("B7").Value 'This works

aryAMSummary(intAMCountTmp).DivMng = Range("B8").Value 'This works

aryAMSummary(intAMCountTmp).TeamType = Range("B9").Value 'This works

aryAMSummary(intAMCountTmp).GMng = Range("B10").Value 'This works

aryAMSummary(intAMCountTmp).LstDay = Range("A" & LstRowNdx +
12).Value 'This works

aryAMSummary(intAMCountTmp).Assigned = Range("B" & LstRowNdx +
14).Value 'This works

aryAMSummary(intAMCountTmp).Scheduled = Range("C" & LstRowNdx +
14).Value 'This puts in the value of Col B (171)

aryAMSummary(intAMCountTmp).Worked = Range("D" & LstRowNdx +
14).Value 'This puts in the value of Col B (171)

aryAMSummary(intAMCountTmp).RPC = Range("E" & LstRowNdx + 14).Value
'This puts in the value of Col B (171)

aryAMSummary(intAMCountTmp).PTP = Range("F" & LstRowNdx + 14).Value
'This puts in the value of Col B (171)

aryAMSummary(intAMCountTmp).SchedP = Range("G" & LstRowNdx +
13).Value 'This puts in the value "1"

aryAMSummary(intAMCountTmp).PenP = Range("H" & LstRowNdx + 13).Value
'This puts in the value "1"

aryAMSummary(intAMCountTmp).ContantP = Range("I" & LstRowNdx +
13).Value 'This puts in the value "1"

aryAMSummary(intAMCountTmp).ConvP = Range("J" & LstRowNdx +
13).Value 'This puts in the value "1"

So here is my problem the Range(address).value is not changing when I
specify a different column. Does anyone see what I am doing wrong?

Thanks,

John

 

[merjet]

Maybe you need to qualify the Range, e.g.:
Worksheets("Sheet1").Range("F" & LstRowNdx + 14).Value
Without the qualification, Excel assumes the active sheet.

HTH,

Merjet