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

J

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[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.
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Sort by date Sort by votes
What are you doing with the OA values? You say that if it begins with OA then to copy it to a variable. That doesn't really do anything in and of itself. Then in step 4 you say that if you encounter a cell that begins with OA, that the variable is reset... The rest is totally doable, but the way you explain it with the OA's doesn't make sense to me.
 
Upvote 0
I need each G/L Account Number (the 6 digit numbers in Column B) to be associated with its respective OA Code / Number. The variable (i.e. the OA Code / Number) needs to be pasted / inserted into Column A whenever Column B contains a string that begins with 6 numbers. Item 2.3. from the above description.

I hope that makes sense. Please let me know if it doesn't.

Thank you.
 
Upvote 0
I think I get it now but am still a little confused with the sample data.

So, we begin going through column b. Then we get to B7 which has "OA21680FY12 - 2 MIPPA AAA". Then we store "OA21680FY12" in a variable. Now we keep moving on through column B. OK, well looking through the sample data at the values that are there, I'm not seeing that would associate with "OA21680FY12". The number portion 21680 doesn't appear in the cells that begin with 6 digit numbers.

See what I mean? Can you tell me how they're associating? Is there a cell in the sample data that it should associate with and be copying into column A? Like "513140 - Medicare Tax" for example?
 
Upvote 0
Or if you mean that each cell that begin with 6 digits will be moved to column A until it reaches a cell that has a different OA value then I think this might be what you're looking for.

Code: 
Sub newtest()
Dim rngBCOL     As Range
Dim cel         As Range
Dim OA          As String
Set rngBCOL = Range("B1", Range("B" & Rows.Count).End(xlUp))
    For Each cel In rngBCOL
        If Left(cel, 2) = "OA" And OA <> Left(cel, 11) Then
            OA = Left(cel, 11)
        ElseIf IsNumeric(Left(cel, 6)) = True Then
            cel.Copy Cells(cel.Row, 1)
        End If
    Next cel
            
End Sub
 
Upvote 0
It's not a number match - they associate by their position in the CSV import. The G/L Account Numbers (the strings that begin with 6 numeric digits in Col B - i.e. 513140 - Medicare Tax) belong to the Project Code (the "OA#####FY##") that precedes them (think of it like a department) in the report.

So in the sample data above: "OA21680FY12" should be pasted / inserted into cells A9:A24 and A27:A29.

In the linked data file (with more data):

  • "OA21680FY12" (from Cell B7) should be pasted / inserted into cells A9:A24 and A27:A34 and A37
  • "OA21681FY12" (from Cell B40) should be pasted / inserted into cells A42:A45 and A48
  • "OA21682FY12" (from Cell B51) should be pasted / inserted into cells A53:A68 and A71:A82 and A85

Thank you for taking a look into this.
 
Upvote 0
OK, and I think that the code I posted a minute ago was close, but looking at you description in the first post that this one will do what you want.

Code: 
Sub newtest()
Dim rngBCOL     As Range
Dim cel         As Range
Dim OA          As String
Set rngBCOL = Range("B1", Range("B" & Rows.Count).End(xlUp))
    For Each cel In rngBCOL
        If Left(cel, 2) = "OA" Then
            OA = Left(cel, 11)
        ElseIf IsNumeric(Left(cel, 6)) = True Then
            Cells(cel.Row, 1) = OA
        End If
    Next cel
            
End Sub
 
Last edited:
Upvote 0
lrobbo314 said:
Or if you mean that each cell that begin with 6 digits will be moved to column A until it reaches a cell that has a different OA value then I think this might be what you're looking for.

Code: 
Sub newtest()
Dim rngBCOL     As Range
Dim cel         As Range
Dim OA          As String
Set rngBCOL = Range("B1", Range("B" & Rows.Count).End(xlUp))
    For Each cel In rngBCOL
        If Left(cel, 2) = "OA" And OA <> Left(cel, 11) Then
            OA = Left(cel, 11)
        ElseIf IsNumeric(Left(cel, 6)) = True Then
            cel.Copy Cells(cel.Row, 1)
        End If
    Next cel
            
End Sub
Click to expand...
No that's not quite it. Those are the right rows in Col A to paste to, but what should be pasted is the 11 digit OA code that precedes them. The first OA code will be repeated over and over in Col A until the next one is encountered (in Col B), and then that next OA code will be repeated in Col A until the next OA code is encountered (in Col B), and so on.
 
Upvote 0
lrobbo314 said:
OK, and I think that the code I posted a minute ago was close, but looking at you description in the first post that this one will do what you want.

Code: 
Sub newtest()
Dim rngBCOL     As Range
Dim cel         As Range
Dim OA          As String
Set rngBCOL = Range("B1", Range("B" & Rows.Count).End(xlUp))
    For Each cel In rngBCOL
        If Left(cel, 2) = "OA" Then
            OA = Left(cel, 11)
        ElseIf IsNumeric(Left(cel, 6)) = True Then
            Cells(cel.Row, 1) = OA
        End If
    Next cel
            
End Sub
Click to expand...
AWESOME! AMAZING! FANTASTIC! :-D

Thank you so much. It works perfectly. I just tested it on the sample file that I had uploaded that contained detail for 3 OA groupings and it worked. Then I tested it on another data file that I have with 27 OA codes and 400 lines of data and it worked like a charm on that one too.

Very much appreciate your time and talents lrobbo314. Thanks!
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,224,550
Messages
6,179,462
Members
452,915
Latest member
hannnahheileen

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top