I need to write a VBA sub for this excel spread sheet here is the data and direc
ID: 3557199 • Letter: I
Question
I need to write a VBA sub for this excel spread sheet here is the data and directions.
Your task: Write a sub to (1) find those customers whose total order amount is greater than $1500, and show these customers' IDs and corresponding total order amount in a separate worksheet (i.e. "Results"), and (2) Sort the results by total amount in ascending order.
The "Orders" worksheet contains the information of order date, customer ID & order amount. Since customers ordered more than once, there are multiple entries of cutomer IDs.Your task: Write a sub to (1) find those customers whose total order amount is greater than $1500, and show these customers' IDs and corresponding total order amount in a separate worksheet (i.e. "Results"), and (2) Sort the results by total amount in ascending order.
Customer orders Order date Customer ID Amount purchased 2-Jan-05 144 $586 2-Jan-05 190 $574 3-Jan-05 120 $1,911 3-Jan-05 145 $593 3-Jan-05 188 $332 3-Jan-05 192 $2,782 4-Jan-05 105 $301 4-Jan-05 111 $286 4-Jan-05 113 $1,290 4-Jan-05 119 $421 4-Jan-05 138 $1,130 4-Jan-05 140 $485 5-Jan-05 129 $584 5-Jan-05 141 $188 5-Jan-05 146 $589 5-Jan-05 164 $1,354 5-Jan-05 168 $272 6-Jan-05 103 $588 6-Jan-05 144 $512 6-Jan-05 158 $127 6-Jan-05 191 $377 7-Jan-05 103 $35 7-Jan-05 115 $840 7-Jan-05 118 $665 7-Jan-05 124 $18 7-Jan-05 135 $244 7-Jan-05 152 $448 7-Jan-05 193 $68 7-Jan-05 195 $685 7-Jan-05 196 $1,655 8-Jan-05 125 $301 8-Jan-05 181 $241 9-Jan-05 108 $979 9-Jan-05 112 $1,175 9-Jan-05 113 $394 9-Jan-05 124 $1,278 9-Jan-05 133 $85 9-Jan-05 136 $175 9-Jan-05 148 $3,242 9-Jan-05 149 $441 9-Jan-05 149 $670 9-Jan-05 153 $690 9-Jan-05 168 $632 9-Jan-05 170 $848 9-Jan-05 171 $228 9-Jan-05 178 $305 9-Jan-05 186 $408 9-Jan-05 192 $150 10-Jan-05 131 $288 10-Jan-05 158 $279 10-Jan-05 162 $543 10-Jan-05 169 $650 10-Jan-05 174 $760 11-Jan-05 107 $499 11-Jan-05 122 $152 11-Jan-05 136 $317 11-Jan-05 191 $231 12-Jan-05 156 $4,440 13-Jan-05 101 $488 13-Jan-05 125 $541 13-Jan-05 155 $116 13-Jan-05 161 $476 13-Jan-05 164 $1,040 13-Jan-05 168 $2,216 13-Jan-05 178 $623 14-Jan-05 155 $123 15-Jan-05 114 $1,867 15-Jan-05 130 $689 15-Jan-05 136 $73 15-Jan-05 156 $173 15-Jan-05 167 $1,258 15-Jan-05 191 $1,205 15-Jan-05 196 $490 16-Jan-05 115 $259 17-Jan-05 112 $1,332 17-Jan-05 122 $231 17-Jan-05 151 $413 17-Jan-05 176 $976 17-Jan-05 179 $339 17-Jan-05 192 $887 17-Jan-05 192 $305 18-Jan-05 174 $368 19-Jan-05 160 $72 20-Jan-05 105 $784 20-Jan-05 157 $662 20-Jan-05 167 $475 20-Jan-05 173 $41 21-Jan-05 120 $1,618 21-Jan-05 134 $249 22-Jan-05 109 $748 22-Jan-05 153 $189 22-Jan-05 154 $253 22-Jan-05 159 $209 22-Jan-05 162 $361 22-Jan-05 188 $419 22-Jan-05 189 $1,056 22-Jan-05 198 $200 23-Jan-05 117 $215 23-Jan-05 120 $335 23-Jan-05 124 $538 23-Jan-05 136 $415 23-Jan-05 142 $132 23-Jan-05 149 $303 23-Jan-05 150 $809 23-Jan-05 150 $156 23-Jan-05 150 $400 23-Jan-05 169 $947 23-Jan-05 171 $487 23-Jan-05 172 $185 23-Jan-05 175 $287 23-Jan-05 176 $294 23-Jan-05 199 $273 24-Jan-05 160 $343 24-Jan-05 195 $164 25-Jan-05 102 $98 25-Jan-05 108 $166 25-Jan-05 123 $782 25-Jan-05 123 $84 25-Jan-05 125 $75 25-Jan-05 133 $235 25-Jan-05 155 $280 25-Jan-05 175 $318 25-Jan-05 183 $215 25-Jan-05 189 $752 25-Jan-05 199 $100 26-Jan-05 119 $1,808 26-Jan-05 165 $474 27-Jan-05 112 $287 27-Jan-05 115 $319 27-Jan-05 117 $1,040 27-Jan-05 155 $279 27-Jan-05 177 $344 28-Jan-05 127 $704 28-Jan-05 142 $4,165 29-Jan-05 122 $252 30-Jan-05 102 $471 30-Jan-05 103 $260 30-Jan-05 183 $120 31-Jan-05 110 $490 31-Jan-05 139 $97 31-Jan-05 154 $85 31-Jan-05 163 $427 31-Jan-05 164 $176 31-Jan-05 167 $406 31-Jan-05 181 $210 31-Jan-05 193 $256 31-Jan-05 199 $1,101 1-Feb-05 101 $259 1-Feb-05 107 $435 2-Feb-05 138 $327 2-Feb-05 167 $895 2-Feb-05 190 $720 3-Feb-05 123 $111 3-Feb-05 153 $362 3-Feb-05 187 $50 3-Feb-05 192 $937 4-Feb-05 116 $703 4-Feb-05 122 $48 4-Feb-05 132 $222 4-Feb-05 182 $441 4-Feb-05 186 $65 4-Feb-05 195 $729 5-Feb-05 103 $2,848 5-Feb-05 105 $281 5-Feb-05 109 $219 5-Feb-05 110 $1,141 5-Feb-05 126 $1,473 5-Feb-05 142 $381 5-Feb-05 149 $1,677 5-Feb-05 150 $76 5-Feb-05 157 $155 5-Feb-05 157 $44 6-Feb-05 106 $434 6-Feb-05 133 $140 6-Feb-05 183 $145 6-Feb-05 183 $129 7-Feb-05 193 $177 8-Feb-05 109 $541 8-Feb-05 129 $824 8-Feb-05 130 $2,255 8-Feb-05 140 $373 8-Feb-05 141 $743 8-Feb-05 196 $706 9-Feb-05 133 $100 10-Feb-05 110 $135 10-Feb-05 115 $161 10-Feb-05 117 $81 10-Feb-05 130 $363 10-Feb-05 132 $838 10-Feb-05 134 $529 10-Feb-05 140 $250 10-Feb-05 145 $67 10-Feb-05 148 $92 10-Feb-05 152 $312 10-Feb-05 153 $1,288 10-Feb-05 156 $44 10-Feb-05 159 $187 10-Feb-05 161 $153 10-Feb-05 162 $1,005 10-Feb-05 165 $118 10-Feb-05 167 $443 10-Feb-05 169 $446 10-Feb-05 170 $125 10-Feb-05 177 $209 10-Feb-05 188 $375 10-Feb-05 188 $99 10-Feb-05 197 $309 10-Feb-05 198 $88 11-Feb-05 102 $360 11-Feb-05 109 $1,039 11-Feb-05 113 $247 11-Feb-05 149 $1,061 11-Feb-05 150 $102 11-Feb-05 150 $4,066 11-Feb-05 152 $586 11-Feb-05 153 $262 11-Feb-05 155 $866 11-Feb-05 160 $352 11-Feb-05 165 $600 11-Feb-05 172 $891 11-Feb-05 174 $676 11-Feb-05 180 $36 11-Feb-05 181 $215 11-Feb-05 191 $429 11-Feb-05 197 $640 12-Feb-05 168 $701 13-Feb-05 168 $2,152 14-Feb-05 143 $273 15-Feb-05 113 $213 15-Feb-05 137 $1,070 15-Feb-05 144 $1,342 15-Feb-05 190 $371 15-Feb-05 195 $502 16-Feb-05 128 $70 16-Feb-05 135 $149 16-Feb-05 169 $30 16-Feb-05 188 $169 17-Feb-05 169 $124 17-Feb-05 192 $129 18-Feb-05 114 $114 18-Feb-05 116 $161 18-Feb-05 117 $1,438 18-Feb-05 120 $82 18-Feb-05 123 $151 18-Feb-05 126 $345 18-Feb-05 127 $66 18-Feb-05 150 $56 18-Feb-05 151 $218 18-Feb-05 153 $119 18-Feb-05 159 $145 18-Feb-05 164 $61 18-Feb-05 179 $336 18-Feb-05 182 $84 18-Feb-05 188 $486 18-Feb-05 192 $438 18-Feb-05 194 $186 18-Feb-05 195 $75 19-Feb-05 110 $102 19-Feb-05 121 $1,264 19-Feb-05 137 $604 19-Feb-05 172 $43 19-Feb-05 184 $152 20-Feb-05 117 $991 20-Feb-05 166 $961 20-Feb-05 168 $440 20-Feb-05 169 $443 20-Feb-05 175 $124 20-Feb-05 177 $207 21-Feb-05 106 $373 21-Feb-05 129 $98 21-Feb-05 133 $307 21-Feb-05 147 $87 21-Feb-05 147 $357 21-Feb-05 156 $1,024 21-Feb-05 157 $107 21-Feb-05 160 $1,046 21-Feb-05 167 $837 21-Feb-05 172 $568 22-Feb-05 105 $535 22-Feb-05 120 $460 22-Feb-05 158 $89 22-Feb-05 160 $650 22-Feb-05 180 $2,244 22-Feb-05 188 $92 22-Feb-05 198 $45 23-Feb-05 162 $166 23-Feb-05 179 $379 23-Feb-05 188 $396 24-Feb-05 109 $20 24-Feb-05 175 $54 25-Feb-05 119 $150 25-Feb-05 124 $481 25-Feb-05 150 $196 25-Feb-05 174 $915 25-Feb-05 176 $129 26-Feb-05 135 $696 26-Feb-05 143 $1,179 26-Feb-05 170 $188 27-Feb-05 141 $848 27-Feb-05 141 $456 27-Feb-05 149 $172 27-Feb-05 152 $126 27-Feb-05 157 $132 27-Feb-05 168 $116 27-Feb-05 199 $163 28-Feb-05 176 $1,500 1-Mar-05 106 $121 1-Mar-05 109 $147 1-Mar-05 127 $65 1-Mar-05 130 $340 1-Mar-05 136 $87 1-Mar-05 164 $492 2-Mar-05 145 $234 3-Mar-05 122 $259 3-Mar-05 134 $140 3-Mar-05 135 $393 3-Mar-05 158 $252 3-Mar-05 191 $156 3-Mar-05 196 $313 4-Mar-05 103 $510 4-Mar-05 123 $227 4-Mar-05 163 $223 5-Mar-05 180 $98 6-Mar-05 159 $827 6-Mar-05 178 $232 6-Mar-05 181 $699 6-Mar-05 188 $60 7-Mar-05 118 $202 7-Mar-05 146 $1,606 7-Mar-05 160 $190 8-Mar-05 143 $46 9-Mar-05 142 $95 9-Mar-05 172 $404 9-Mar-05 179 $233 9-Mar-05 185 $205 10-Mar-05 117 $145 10-Mar-05 132 $538 10-Mar-05 146 $462 10-Mar-05 169 $90 11-Mar-05 102 $328 11-Mar-05 102 $2,334 11-Mar-05 111 $93 11-Mar-05 121 $47 11-Mar-05 165 $167 11-Mar-05 166 $199 11-Mar-05 171 $398 11-Mar-05 174 $25 12-Mar-05 161 $55 13-Mar-05 187 $151 14-Mar-05 162 $254 15-Mar-05 105 $197 15-Mar-05 107 $923 15-Mar-05 112 $130 15-Mar-05 164 $345 16-Mar-05 123 $1,193 16-Mar-05 129 $189 16-Mar-05 137 $197 16-Mar-05 138 $96 16-Mar-05 156 $539 16-Mar-05 158 $983 16-Mar-05 159 $1,037 16-Mar-05 186 $893 17-Mar-05 110 $1,626 17-Mar-05 110 $484 17-Mar-05 121 $347 17-Mar-05 142 $489 17-Mar-05 147 $173 17-Mar-05 163 $325 17-Mar-05 177 $314 17-Mar-05 182 $712 17-Mar-05 187 $936 17-Mar-05 194 $1,249 18-Mar-05 107 $634 18-Mar-05 112 $167 18-Mar-05 142 $350 18-Mar-05 152 $249 18-Mar-05 171 $90 18-Mar-05 188 $590 19-Mar-05 103 $678 19-Mar-05 198 $136 20-Mar-05 104 $1,008 20-Mar-05 110 $2,064 20-Mar-05 114 $166 20-Mar-05 130 $279 20-Mar-05 165 $835 21-Mar-05 170 $97 21-Mar-05 185 $882 21-Mar-05 187 $402 21-Mar-05 188 $105 22-Mar-05 125 $359 22-Mar-05 140 $339 22-Mar-05 148 $77 22-Mar-05 150 $538 22-Mar-05 153 $64 22-Mar-05 166 $649 22-Mar-05 179 $858 22-Mar-05 186 $222 22-Mar-05 194 $872 23-Mar-05 124 $80 23-Mar-05 130 $84 23-Mar-05 135 $272 23-Mar-05 139 $236 23-Mar-05 153 $739 23-Mar-05 165 $161 23-Mar-05 174 $533 23-Mar-05 175 $363 23-Mar-05 191 $304 24-Mar-05 129 $529 25-Mar-05 138 $54 25-Mar-05 164 $203 26-Mar-05 125 $565 26-Mar-05 151 $312 26-Mar-05 158 $486 26-Mar-05 199 $108 27-Mar-05 152 $51 27-Mar-05 168 $399 27-Mar-05 199 $436 28-Mar-05 126 $171 28-Mar-05 145 $450 28-Mar-05 147 $597 28-Mar-05 175 $1,083 28-Mar-05 184 $862 28-Mar-05 191 $292 28-Mar-05 192 $444 29-Mar-05 126 $456 29-Mar-05 136 $421 29-Mar-05 139 $540 29-Mar-05 147 $123 29-Mar-05 151 $188 29-Mar-05 171 $268 29-Mar-05 172 $34 29-Mar-05 182 $174 29-Mar-05 183 $166 30-Mar-05 131 $364 30-Mar-05 159 $432 30-Mar-05 174 $495 30-Mar-05 175 $334 30-Mar-05 176 $5,169 30-Mar-05 198 $190 31-Mar-05 194 $119 1-Apr-05 131 $826 1-Apr-05 140 $315 1-Apr-05 187 $477 2-Apr-05 103 $73 2-Apr-05 118 $414 2-Apr-05 147 $519 2-Apr-05 149 $1,726 2-Apr-05 150 $1,052 2-Apr-05 153 $374 2-Apr-05 158 $771 2-Apr-05 165 $202 2-Apr-05 190 $203 2-Apr-05 197 $815 3-Apr-05 157 $24 4-Apr-05 128 $167 4-Apr-05 141 $675 4-Apr-05 155 $127 5-Apr-05 137 $631 5-Apr-05 138 $158 5-Apr-05 148 $380 5-Apr-05 154 $79 5-Apr-05 168 $383 5-Apr-05 186 $144 6-Apr-05 138 $85 6-Apr-05 185 $273 7-Apr-05 103 $101 7-Apr-05 143 $359 7-Apr-05 198 $162 8-Apr-05 141 $279 8-Apr-05 145 $365 8-Apr-05 162 $305 8-Apr-05 163 $532 8-Apr-05 181 $55 9-Apr-05 133 $204 9-Apr-05 157 $293 9-Apr-05 158 $313 9-Apr-05 171 $488 9-Apr-05 191 $109 10-Apr-05 102 $52 10-Apr-05 118 $400 10-Apr-05 126 $438 10-Apr-05 133 $172 10-Apr-05 180 $25 10-Apr-05 185 $236 11-Apr-05 125 $343 11-Apr-05 159 $209 11-Apr-05 178 $1,341 11-Apr-05 182 $829 12-Apr-05 154 $866 12-Apr-05 159 $765 13-Apr-05 142 $1,192 13-Apr-05 177 $438 14-Apr-05 105 $625 14-Apr-05 109 $1,070 14-Apr-05 116 $153 14-Apr-05 117 $575 14-Apr-05 172 $552 14-Apr-05 184 $52 15-Apr-05 132 $796 15-Apr-05 164 $1,000 15-Apr-05 166 $564 16-Apr-05 108 $148 17-Apr-05 110 $632 17-Apr-05 128 $226 17-Apr-05 173 $1,936 18-Apr-05 137 $431 19-Apr-05 124 $254 19-Apr-05 186 $704 19-Apr-05 193 $419 19-Apr-05 198 $274 20-Apr-05 131 $528 20-Apr-05 136 $1,079 20-Apr-05 151 $378 20-Apr-05 182 $372 20-Apr-05 191 $204 21-Apr-05 183 $206 22-Apr-05 180 $830 23-Apr-05 117 $33 23-Apr-05 127 $169 23-Apr-05 129 $686 23-Apr-05 136 $129 23-Apr-05 148 $641 23-Apr-05 161 $160 23-Apr-05 174 $384 23-Apr-05 190 $272 23-Apr-05 198 $146 24-Apr-05 126 $122 24-Apr-05 128 $59 24-Apr-05 130 $245 24-Apr-05 131 $212 24-Apr-05 142 $643 24-Apr-05 144 $288 24-Apr-05 149 $476 24-Apr-05 150 $656 24-Apr-05 158 $528 24-Apr-05 158 $1,255 24-Apr-05 167 $171 24-Apr-05 173 $180 24-Apr-05 177 $504 24-Apr-05 188 $543 2/29/2005 104 $744 2/29/2005 107 $1,557 2/29/2005 114 $349 2/29/2005 115 $69 2/29/2005 156 $646 2/29/2005 170 $86Explanation / Answer
Sub Macro2()
Dim Index As Integer
Sheets("Results").Select
Cells(1, 1).Value = "Customer ID"
Cells(1, 2).Value = "Amount purchased"
Index = 2
For counter = 2 To 300
Set curCell = Worksheets("Orders").Cells(counter, 3)
If Abs(curCell.Value) > 1500 Then
Set custId = Worksheets("Orders").Cells(counter, 2)
Sheets("Results").Select
Cells(Index, 1).Value = custId
Cells(Index, 2).Value = curCell
Index = Index + 1
End If
Next counter
Range("A2:B300").Select
ActiveWorkbook.Worksheets("Results").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Results").Sort.SortFields.Add Key:=Range("B2:B300") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Results").Sort
.SetRange Range("A1:B300")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub