daltendavis
New Member
- Joined
- Jun 26, 2018
- Messages
- 37
I am currently having to manually count how many times a number appears in a single column ranging from 25-50 rows long. I need a VBA code that allows me to take the numbers in the pasted column and not only count them but apply a value to them. For Example:
Pasted on sheet 1 Column A:
111
222-*333
444-555-777
333-*555
That would be an example of a shortened version of something pasted. I need it to return values like so on sheet 2:
<tbody>
</tbody>
Essentially if a number is by itself it represents a single truck, if there are 2 numbers in a single cell it represents 2 stores on a single truck, 3 3 stores on a single truck, using 33% of said truck.
In the set I will be pasting sometimes it reads "OFF" or a string of letters in a cell, I do not need that, also when a store on a truck is split it uses an * in front which is what is seemingly making the count so difficult. If there is any more information I can give feel free to ask. This is a daily dispatch sheet that I receive everyday. An example of an entire column I will paste is as follows:
<colgroup><col></colgroup><tbody>
</tbody>
It varies on a daily basis and sometimes can get up to 70 rows, however is always in a single column. I have a clear macro button set up so the Idea is to put this macro onto a button and essentially paste, take value of what I need on a separate workbook, clear the column, and paste the next day continuing the process.
Thanks in advance
Pasted on sheet 1 Column A:
111
222-*333
444-555-777
333-*555
That would be an example of a shortened version of something pasted. I need it to return values like so on sheet 2:
| A | B |
| 111 | 1 |
| 222 | .5 |
| 333 | 1 |
| 444 | .33 |
| 555 | .83 |
| 777 | .33 |
<tbody>
</tbody>
Essentially if a number is by itself it represents a single truck, if there are 2 numbers in a single cell it represents 2 stores on a single truck, 3 3 stores on a single truck, using 33% of said truck.
In the set I will be pasting sometimes it reads "OFF" or a string of letters in a cell, I do not need that, also when a store on a truck is split it uses an * in front which is what is seemingly making the count so difficult. If there is any more information I can give feel free to ask. This is a daily dispatch sheet that I receive everyday. An example of an entire column I will paste is as follows:
| CLM CHEP |
| CLM CHEP |
| 703 - 551 |
| 925 - 909 |
| OFF |
| OFF |
| OFF |
| 975 - 153 |
| 705 - 879 |
| 1 Pal WC Meats - 849 - 531 |
| 461 - 502 |
| 597 - 683 |
| OFF |
| OFF |
| 021 - 099 |
| 093 - 386 |
| 841 - 048 |
| OFF |
| 118 - 938 |
| 102 - 407 |
| 167 - 900 |
| 657 - 699 |
| OFF |
| 014 - 019 |
| OFF |
| 132 - 073 |
| *022 - 136 - 164 |
| 077 |
| 508 |
| WEST ROCK |
| 409 - 888 |
| 719 |
| 933 - 983 |
| 395 |
| OFF |
| 015 - 790 |
| 049 - 043 |
| 829 - 022 |
| 124 - 470 |
| 850 - 788 |
| 058 - 912 |
| OFF |
| 941 - 940 |
| 137 - 989 |
| CLM CHEP |
<colgroup><col></colgroup><tbody>
</tbody>
It varies on a daily basis and sometimes can get up to 70 rows, however is always in a single column. I have a clear macro button set up so the Idea is to put this macro onto a button and essentially paste, take value of what I need on a separate workbook, clear the column, and paste the next day continuing the process.
Thanks in advance
