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

	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>

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.

 

[lrobbo314]

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.

 

[Jeff Meyers]

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.

 

[lrobbo314]

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?

 

[lrobbo314]

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.

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

 

[Jeff Meyers]

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.

 

[lrobbo314]

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.

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

 

[Jeff Meyers]

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.

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

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.

 

[lrobbo314]

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

 

[Jeff Meyers]

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.

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

AWESOME! AMAZING! FANTASTIC!

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!