Excel VBA
Using the worksheet Data in the attached file, write a sub that will do the following: a). use cell A3 as an anchor cell b). locate the cell where the word Bins is located c). declare a dynamic array (name it Bins) as a single and a dynamic array (name it Cnt) as an integer
d). count the number of bins using range properties (i.e., count, etc) e). re-dimension the array Bins to have the number of bins specified in the worksheet and determine in d) f). re-dimension the array Cnt to have the number of bins plus one g). write a sub that will count the number of times the amount purchased is below the first value in the Bins array and place in the first element of the array Cnt, the number of times the amount purchased is greater than the first value and less than or equal to the second value in the Bins array and place it in the second element of array Cnt, and so on. The last element of the array Cnt will include the number of times the amount purchased exceeds the last value in the Bins array. h). write next to the column for the Bins, the numbers collected in the Cnt array. Note that the Cnt array has one more element than the Bins array. Your code should run for a different number of Bins and a different data set with A3 as anchor cell.
Excel data is too large but in the screen cap is some of the data to use as a test case if needed but the final counts would be different than in the other screen cap.
4). Using the worksheet Data in the attached file, write a sub that will do the following: a). use cell A3 as an anchor cell b). locate the cell where the word Bins is located c). declare a dynamic array (name it Bins) as a single and a dynamic array (name it Cnt) as an integer d). count the number of bins using range properties (i.e., count, etc) e) re-dimension the array Bins to have the number of bins specified in the worksheet and determine in d) f). re-dimension the array Cnt to have the number of bins plus one g). write a sub that will count the number of times the "amount purchased is below the first value in the Bins array and place in the first element of the array Cnt, the number of times the "amount purchased is greater than the first value and less than or equal to the second value in the Bins array and place it in the second element of array Cnt, and so on. The last element of the array Cnt will include the number of times the amount purchased" exceeds the last value in the Bins array. h). write next to the column for the Bins, the numbers collected in the Cnt array. Note that the Cnt array has one more element than the Bins array. Your code should run for a different number of Bins and a different data set with A3 as anchor cell. For the example given in the worksheet, the column for Counts will look as follows: Bins 100 500 Counts 75 294 700 1000 63 53 61 Bins Counts 100 75 500 294 700 63 53 1000 61 1 Customer orders 2 3 Order date Customer ID 4 2-Jan-05 190 5 2-Jan-05 144 6 3-Jan-05 120 7 3-Jan-05 192 8 3-Jan-05 145 9 3-Jan-05 188 10 4-Jan-05 113 11 4-Jan-05 140 12 4-Jan-05 105 13 4-Jan-05 138 14 4-Jan-05 111 15 4-Jan-05 119 16 5-Jan-05 168 17 5-Jan-05 146 18 5-Jan-05 141 19 5-Jan-05 129 20 5-Jan-05 164 21 6-Jan-05 158 22 6-Jan-05 103 23 6-Jan-05 144 24 6-Jan-05 191 25 7-Jan-05 103 26 7-Jan-05 195 27 7-Jan-05 118 28 7-Jan-05 124 29 7-Jan-05 152 30 7-Jan-05 193 31 7-Jan-05 196 32 7-Jan-05 115 33 7-Jan-05 135 34 8-Jan-05 125 35 8-Jan-05 181 36 9-Jan-05 112 37 9-Jan-05 148 38 9-Jan-05 186 39 9-Jan-05 124 Amount purchas $586 $574 $1,911 $593 $332 $2,782 $301 $286 $1,290 $421 $1,130 $485 $584 $188 $589 $1,354 $272 $588 $512 $127 $377 $35 $840 $665 $18 $244 $448 $68 $685 $1,655 $301 $241 $979 $1,175 $394 $1,278 4). Using the worksheet Data in the attached file, write a sub that will do the following: a). use cell A3 as an anchor cell b). locate the cell where the word Bins is located c). declare a dynamic array (name it Bins) as a single and a dynamic array (name it Cnt) as an integer d). count the number of bins using range properties (i.e., count, etc) e) re-dimension the array Bins to have the number of bins specified in the worksheet and determine in d) f). re-dimension the array Cnt to have the number of bins plus one g). write a sub that will count the number of times the "amount purchased is below the first value in the Bins array and place in the first element of the array Cnt, the number of times the "amount purchased is greater than the first value and less than or equal to the second value in the Bins array and place it in the second element of array Cnt, and so on. The last element of the array Cnt will include the number of times the amount purchased" exceeds the last value in the Bins array. h). write next to the column for the Bins, the numbers collected in the Cnt array. Note that the Cnt array has one more element than the Bins array. Your code should run for a different number of Bins and a different data set with A3 as anchor cell. For the example given in the worksheet, the column for Counts will look as follows: Bins 100 500 Counts 75 294 700 1000 63 53 61 Bins Counts 100 75 500 294 700 63 53 1000 61 1 Customer orders 2 3 Order date Customer ID 4 2-Jan-05 190 5 2-Jan-05 144 6 3-Jan-05 120 7 3-Jan-05 192 8 3-Jan-05 145 9 3-Jan-05 188 10 4-Jan-05 113 11 4-Jan-05 140 12 4-Jan-05 105 13 4-Jan-05 138 14 4-Jan-05 111 15 4-Jan-05 119 16 5-Jan-05 168 17 5-Jan-05 146 18 5-Jan-05 141 19 5-Jan-05 129 20 5-Jan-05 164 21 6-Jan-05 158 22 6-Jan-05 103 23 6-Jan-05 144 24 6-Jan-05 191 25 7-Jan-05 103 26 7-Jan-05 195 27 7-Jan-05 118 28 7-Jan-05 124 29 7-Jan-05 152 30 7-Jan-05 193 31 7-Jan-05 196 32 7-Jan-05 115 33 7-Jan-05 135 34 8-Jan-05 125 35 8-Jan-05 181 36 9-Jan-05 112 37 9-Jan-05 148 38 9-Jan-05 186 39 9-Jan-05 124 Amount purchas $586 $574 $1,911 $593 $332 $2,782 $301 $286 $1,290 $421 $1,130 $485 $584 $188 $589 $1,354 $272 $588 $512 $127 $377 $35 $840 $665 $18 $244 $448 $68 $685 $1,655 $301 $241 $979 $1,175 $394 $1,278
