Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems populating an array
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems populating an array
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Populating an array | Excel Worksheet Functions | |||
Populating a dropdown list with an Array | Excel Programming | |||
Populating a 2-D array | Excel Programming | |||
Populating a Temporary Array | Excel Programming | |||
populating multicolumn listbox with an array instead of... | Excel Programming |