Jeff Meyers
Active Member
- Joined
- Mar 14, 2012
- Messages
- 405
I now think that my last request for assistance was too broad in scope (complete project vs a component of the project), and therefore did not receive much attention and assistance. Here's the link to that thread:
http://www.mrexcel.com/forum/showth...mat-It-via-VBA-and-Output-a-Usable-Data-Table
So now I'm trying to accomplish just one task - and it's a task that I cannot complete via the Macro Recorder so I really need some assistance with the VBA code.
Here's a sample of my data (it's from a CSV import):
CSV Import
<tbody>
</tbody>Excel 2010
Here's what I would like to have happen via VBA:
Here's a link to a data file to utilize: https://www.box.com/s/ec9115fd50eca5c830d9
Would anyone please provide some assistance / guidance with this step in the process. It would be greatly appreciated.
http://www.mrexcel.com/forum/showth...mat-It-via-VBA-and-Output-a-Usable-Data-Table
So now I'm trying to accomplish just one task - and it's a task that I cannot complete via the Macro Recorder so I really need some assistance with the VBA code.
Here's a sample of my data (it's from a CSV import):
CSV Import
A | B | C | D | |
---|---|---|---|---|
1 | ||||
2 | Report ID: RVPCA341.SQR | Page No. 1 | ||
3 | Acct Dt Range 2012 Period 1 - 12 | PROJECT/GRANT SUMMARY | Run Date 07/10/2012 | |
4 | Project Type(s) OA216 | Run Time 15:25:20 | ||
5 | TYPE | PROJ | APPROP | |
6 | OA216 - MIPPA | |||
7 | OA21680FY12 - 2 MIPPA AAA | |||
8 | Salaries and Employee Benefits | |||
9 | 510040 - Regular Salaries | 20,636.00 | 9,037.67 | |
10 | 510320 - Temporary Salaries | 18,549.98 | ||
11 | 510330 - TAP Salaries | 198 | ||
12 | 510520 - Bilingual Pay | 478.32 | ||
13 | 513000 - Retirement-Misc. | 1,987.85 | ||
14 | 513001 - Retirement Debt Srvs - Misc. | 356.72 | ||
15 | 513120 - Social Security | 967.69 | ||
16 | 513140 - Medicare Tax | 226.31 | ||
17 | 515040 - Flex Benefit Plan | 3,152.22 | ||
18 | 515100 - Life Insurance | 11.39 | ||
19 | 515220 - Short Term Disability | 115.71 | ||
20 | 515260 - Unemployment Insurance | 160.39 | ||
21 | 517000 - Workers Comp Insurance | 116 | 105.71 | |
22 | 518100 - Budgeted Benefits | 9,678.00 | 2,826.49 | |
23 | 518150 - LIUNA Health & Safety | 7.19 | ||
24 | 518180 - Other Post Employment Benefits | 38.03 | ||
25 | TTLS for Salaries and Employee Benefits | 30,628.00 | 38,021.67 | |
26 | Services and Supplies | |||
27 | 523700 - Office Supplies | 2,000.00 | ||
28 | 523720 - Photocopying | 1,754.00 | ||
29 | 523760 - Postage-Mailing | 601.59 | ||
30 | ------------------------------------------ | ---------------------------------------------------- | ---------------------------- | ---------------------------------------- |
<tbody>
</tbody>
Here's what I would like to have happen via VBA:
- Step through each cell in Column B from the top to the bottom (last row of data). Column B will contain a variable number of rows.
- With Column B:
- If a blank cell: do nothing
- If the string in a cell begins with "OA": copy the first 11 characters (including the OA - always in the format "OA#####FY##") to a variable
- If the string in a cell begins with 6 numbers: paste the variable in the same row but in Column A
- If the cell contains anything else: skip it and proceed to the next cell
- Continue through each cell in Column B in the same manner
- Whenever a cell with a string that begins with "OA" is encountered, then the variable is reset and the process continues
Here's a link to a data file to utilize: https://www.box.com/s/ec9115fd50eca5c830d9
Would anyone please provide some assistance / guidance with this step in the process. It would be greatly appreciated.