Excel vba: Automated Search/Copy/Paste

[EverClear]

Hi there!!

I have a “soft copy” of an 80-page report that was downloaded from an older mainframe system. The soft copy was in a .txt format, and I imported it into Excel using fixed-width delimiters for the columns. Since the report is 80-pages, it translates to almost 2,000 rows of data in Excel. An extremely truncated version of the report appears below as a sample…</SPAN>

As shown below, the companies listed on the report are grouped into “associations” that have unique association id numbers that only appear in the header of each page of the report in a single cell (example "ASSOCIATION : 1234567890").Each association’s section of the report has 2 subtotal rows called “Association Total.” </SPAN>

My dilemma - Instead of doing a manual copy/paste through 1,700+ rows of data, I need to find a way (either with an Excel formula or with vba code) to COPY each association’s unique id number from the HEADER row to the first of the 2 rows that show the “Association Total” subtotal.


</SPAN></SPAN>

REPORT #</SPAN>			REPOR</SPAN>	T NAME</SPAN>			REPORT DATE</SPAN>	PAGE 1</SPAN></SPAN> <TBODY> </TBODY>
JOB #</SPAN>			REPOR</SPAN>	T HEADER</SPAN>
</SPAN> REGION : REGION NAME</SPAN></SPAN> <TBODY> </TBODY>			SUMMARY</SPAN>	FO</SPAN>	R CURR</SPAN>	ENT MONTH</SPAN>
ASSOCIATION : 34567890</SPAN>	I want to copy	THIS	from the	header....
							TOTAL</SPAN>
COMPANY</SPAN>	COMPANY</SPAN>		TOTAL</SPAN>			DOLLAR</SPAN>	NET</SPAN>
NAME</SPAN>	NO.</SPAN>			INVOICED</SPAN>		EXCESS</SPAN>	ADJUSTMENT</SPAN>	INVOICED</SPAN>
COMPANY A1</SPAN>	###</SPAN>	157</SPAN>	0</SPAN>	40,260.00</SPAN>	99,999</SPAN>	0</SPAN>		40,260.00</SPAN>
COMPANY A2</SPAN>	###</SPAN>	150</SPAN>	0</SPAN>	17,450.00</SPAN>	99,999</SPAN>	0</SPAN>		17,450.00</SPAN>
COMPANY A3</SPAN>	###</SPAN>	118</SPAN>	0</SPAN>	10,002.00</SPAN>	99,999</SPAN>	0</SPAN>		10,002.00</SPAN>
COMPANY A4</SPAN>	###</SPAN>	107</SPAN>	0</SPAN>	20,792.00</SPAN>	99,999</SPAN>	0</SPAN>		20,792.00</SPAN>
</SPAN> COMPANY A5</SPAN></SPAN> <TBODY> </TBODY>	###</SPAN>	148</SPAN>	0</SPAN>	9,427.00</SPAN>	99,999</SPAN>	0</SPAN>		9,427.00</SPAN>
ASSOCIATION TOTAL</SPAN>	to HERE!!	680</SPAN>		97,931.00</SPAN>		0</SPAN>		97,931.00</SPAN>
ASSOCIATION TOTAL</SPAN>	TOTAL DEBI</SPAN>	TS=</SPAN>	121 TOT</SPAN>	AL CREDITS=</SPAN>	0 NET F</SPAN>			</SPAN> <TBODY> </TBODY>
REPORT #</SPAN>			REPOR</SPAN>	T NAME</SPAN>			REPORT DATE</SPAN>	PAGE 2</SPAN>
JOB #</SPAN>			REPOR</SPAN>	T HEADER</SPAN>
</SPAN> REGION : REGION NAME</SPAN></SPAN> <TBODY> </TBODY>			SUMMARY</SPAN>	FO</SPAN>	R CURR</SPAN>	ENT MONTH</SPAN>
ASSOCIATION : 5678901</SPAN>	NEW ID#	I want	to copy	THIS from the	HEADER
							TOTAL</SPAN>
COMPANY</SPAN>	COMPANY</SPAN>		TOTAL</SPAN>			DOLLAR</SPAN>	NET</SPAN>
NAME</SPAN>	NO.</SPAN>			INVOICED</SPAN>		EXCESS</SPAN>	ADJUSTMENT</SPAN>	INVOICED</SPAN>
COMPANY B6</SPAN>	###</SPAN>	228</SPAN>	0</SPAN>	24,558.00</SPAN>	99,999</SPAN>	0</SPAN>		24,558.00</SPAN>
COMPANY B7</SPAN>	###</SPAN>	86</SPAN>	0</SPAN>	31,257.00</SPAN>	99,999</SPAN>	0</SPAN>		31,257.00</SPAN>
COMPANY B8</SPAN>	###</SPAN>	135</SPAN>	0</SPAN>	33,922.00</SPAN>	99,999</SPAN>	0</SPAN>		33,922.00</SPAN>
COMPANY B9</SPAN>	###</SPAN>	85</SPAN>	0</SPAN>	5,807.00</SPAN>	99,999</SPAN>	0</SPAN>		5,807.00</SPAN>
COMPANY B10</SPAN>	###</SPAN>	149</SPAN>	0</SPAN>	49,828.00</SPAN>	99,999</SPAN>	0</SPAN>		49,828.00</SPAN>
COMPANY B11</SPAN>	###</SPAN>	121</SPAN>	0</SPAN>	30,245.00</SPAN>	99,999</SPAN>	0</SPAN>		30,245.00</SPAN>
COMPANY B12</SPAN>	###</SPAN>	33</SPAN>	0</SPAN>	23,988.00</SPAN>	99,999</SPAN>	0</SPAN>		23,988.00</SPAN>
COMPANY B13</SPAN>	###</SPAN>	127</SPAN>	0</SPAN>	39,978.00</SPAN>	99,999</SPAN>	0</SPAN>		39,978.00</SPAN>
COMPANY B14</SPAN>	###</SPAN>	227</SPAN>	0</SPAN>	32,909.00</SPAN>	99,999</SPAN>	0</SPAN>		32,909.00</SPAN>
COMPANY B15</SPAN>	###</SPAN>	44</SPAN>	0</SPAN>	48,601.00</SPAN>	99,999</SPAN>	0</SPAN>		48,601.00</SPAN>
</SPAN> COMPANY B16</SPAN></SPAN> <TBODY> </TBODY>	###</SPAN>	127</SPAN>	0</SPAN>	47,286.00</SPAN>	99,999</SPAN>	0</SPAN>		47,286.00</SPAN>
ASSOCIATION TOTAL</SPAN>	...to HERE!!	1362</SPAN>		368,379.00</SPAN>		0</SPAN>		368,379.00</SPAN>
ASSOCIATION TOTAL</SPAN>	TOTAL DEBI</SPAN>	TS=</SPAN>	397 TOT</SPAN>	AL CREDITS=</SPAN>	0 NET F</SPAN>

<TBODY>
</TBODY>

 

[C Moore]

It's hard to tell where everything is exactly at, but maybe this will be enough for you to change as necessary.

Sub find_place()
    Set colA = Columns(1)
    num_associations = WorksheetFunction.CountIf(colA, "Association:")
    Range("a1").Select
    For i = 1 To num_associations
    Cells.Find(what:="Association:", after:=ActiveCell).Select
    Selection.Offset(1, 0).Copy
    Cells.Find(what:="Association Total", after:=ActiveCell).Select
    Selection.Offset(0, 1).PasteSpecial
    Next i
End Sub

 

[EverClear]

Thanks! I'll give this a try!!