Need Assistance with VBA Code - Cannot Accomplish Task via Macro Recorder

[Jeff Meyers]

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[TABLE="class: html-maker-worksheet"]
<tbody>[TR]
[TH][/TH]
[TH]A
[/TH]
[TH]B
[/TH]
[TH]C
[/TH]
[TH]D
[/TH]
[/TR]
[TR]
[TH]1
[/TH]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]2
[/TH]
[TD]Report ID: RVPCA341.SQR
[/TD]
[TD][/TD]
[TD]Page No. 1
[/TD]
[TD][/TD]
[/TR]
[TR]
[TH]3
[/TH]
[TD]Acct Dt Range 2012 Period 1 - 12
[/TD]
[TD]PROJECT/GRANT SUMMARY
[/TD]
[TD]Run Date 07/10/2012
[/TD]
[TD][/TD]
[/TR]
[TR]
[TH]4
[/TH]
[TD]Project Type(s) OA216
[/TD]
[TD][/TD]
[TD]Run Time 15:25:20
[/TD]
[TD][/TD]
[/TR]
[TR]
[TH]5
[/TH]
[TD]TYPE
[/TD]
[TD][/TD]
[TD]PROJ
[/TD]
[TD]APPROP
[/TD]
[/TR]
[TR]
[TH]6
[/TH]
[TD]OA216 - MIPPA
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]7
[/TH]
[TD][/TD]
[TD]OA21680FY12 - 2 MIPPA AAA
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TH]8
[/TH]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Salaries and Employee Benefits
[/TD]
[/TR]
[TR]
[TH]9
[/TH]
[TD][/TD]
[TD]510040 - Regular Salaries
[/TD]
[TD="align: right"]20,636.00
[/TD]
[TD="align: right"]9,037.67
[/TD]
[/TR]
[TR]
[TH]10
[/TH]
[TD][/TD]
[TD]510320 - Temporary Salaries
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]18,549.98
[/TD]
[/TR]
[TR]
[TH]11
[/TH]
[TD][/TD]
[TD]510330 - TAP Salaries
[/TD]
[TD="align: right"]198
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TH]12
[/TH]
[TD][/TD]
[TD]510520 - Bilingual Pay
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]478.32
[/TD]
[/TR]
[TR]
[TH]13
[/TH]
[TD][/TD]
[TD]513000 - Retirement-Misc.
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1,987.85
[/TD]
[/TR]
[TR]
[TH]14
[/TH]
[TD][/TD]
[TD]513001 - Retirement Debt Srvs - Misc.
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]356.72
[/TD]
[/TR]
[TR]
[TH]15
[/TH]
[TD][/TD]
[TD]513120 - Social Security
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]967.69
[/TD]
[/TR]
[TR]
[TH]16
[/TH]
[TD][/TD]
[TD]513140 - Medicare Tax
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]226.31
[/TD]
[/TR]
[TR]
[TH]17
[/TH]
[TD][/TD]
[TD]515040 - Flex Benefit Plan
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3,152.22
[/TD]
[/TR]
[TR]
[TH]18
[/TH]
[TD][/TD]
[TD]515100 - Life Insurance
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]11.39
[/TD]
[/TR]
[TR]
[TH]19
[/TH]
[TD][/TD]
[TD]515220 - Short Term Disability
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]115.71
[/TD]
[/TR]
[TR]
[TH]20
[/TH]
[TD][/TD]
[TD]515260 - Unemployment Insurance
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]160.39
[/TD]
[/TR]
[TR]
[TH]21
[/TH]
[TD][/TD]
[TD]517000 - Workers Comp Insurance
[/TD]
[TD="align: right"]116
[/TD]
[TD="align: right"]105.71
[/TD]
[/TR]
[TR]
[TH]22
[/TH]
[TD][/TD]
[TD]518100 - Budgeted Benefits
[/TD]
[TD="align: right"]9,678.00
[/TD]
[TD="align: right"]2,826.49
[/TD]
[/TR]
[TR]
[TH]23
[/TH]
[TD][/TD]
[TD]518150 - LIUNA Health & Safety
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]7.19
[/TD]
[/TR]
[TR]
[TH]24
[/TH]
[TD][/TD]
[TD]518180 - Other Post Employment Benefits
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]38.03
[/TD]
[/TR]
[TR]
[TH]25
[/TH]
[TD][/TD]
[TD]TTLS for Salaries and Employee Benefits
[/TD]
[TD="align: right"]30,628.00
[/TD]
[TD="align: right"]38,021.67
[/TD]
[/TR]
[TR]
[TH]26
[/TH]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Services and Supplies
[/TD]
[/TR]
[TR]
[TH]27
[/TH]
[TD][/TD]
[TD]523700 - Office Supplies
[/TD]
[TD="align: right"]2,000.00
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TH]28
[/TH]
[TD][/TD]
[TD]523720 - Photocopying
[/TD]
[TD="align: right"]1,754.00
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TH]29
[/TH]
[TD][/TD]
[TD]523760 - Postage-Mailing
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]601.59
[/TD]
[/TR]
[TR]
[TH]30
[/TH]
[TD]------------------------------------------
[/TD]
[TD]----------------------------------------------------
[/TD]
[TD]----------------------------
[/TD]
[TD]----------------------------------------
[/TD]
[/TR]
</tbody>[/TABLE]
Excel 2010

Here's what I would like to have happen via VBA:

1. 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.
2. With Column B:
   1. If a blank cell: do nothing
   2. 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
   3. If the string in a cell begins with 6 numbers: paste the variable in the same row but in Column A
   4. If the cell contains anything else: skip it and proceed to the next cell
3. Continue through each cell in Column B in the same manner
4. 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.

 

[Jeff Meyers]

Check my last post. I think we've got it.

You definitely did. Thanks again.

 

[lrobbo314]

Great! Glad to help.