VBA noob. Need help with reformatting compromised data

I have been tasked with reformatting a very large report. The problem is, the source file did not maintain its integrity when the user transferred it to Excel. Now, there are almost 7000 individual units that will need to be reformatted manually. Rather than working each individual cell, I have been attempting to create a macro to save me 60+ hours of work, however I haven't had much luck. Let me explain what I'm trying to do:

The reports consists of upwards of 150,000 rows, all under column A. This is the basic layout throughout the reports:

A
1 RUN DATE ##/##/## NAME OF BUSINESS PAGE #
2 RUN TIME ##:##
3
4
5 FIELD 1: #### DESCRIPTION
6
7
8 FIELD 2: DESCRIPTION
9
10
...
22 FIELD 7: DESCIRIPTION
23 DESCRIPTION CONTINUED
24 DESCRIPTION CONTINUED
25 DESCRIPTION CONTINUED
26
27
28
29 FIELD 1: #### DESCRIPTION
... and so on.

Additionally, "Field 7" will always begin with the text "DOCUMENTATION:". Field 7 may be anywhere from 1 - 7 different rows. Sometimes, if the page ends immediately after FIELD 7, the time/date stamp will be placed before FIELD 1. So, Field 7 will be immediately followed by either "EDIT:" or "RUN DATE". What I need to do, is take everything in "FIELD 7", for each item, and paste it into Column A on sheet 2 or in a column on that page. Either would be fine. However, I am having trouble creating a macro to perform this function and was hoping someone might be able to lend me some/plenty of assistance.

The end result of this report would ideally look like this:
A
1 "FIELD 7 DESCRIPTION"
2 "FIELD 7 DESCRIPTION"
3 "FIELD 7 DESCRIPTION"
...
7000 "FIELD 7 DESCRIPTION"

I appreciate any help you might be able to lend. Let me know if there is anything that isn't clear. I'll continue to experiment with this and provide updates as they come.

Thank you.

I made the change, but I'm still getting the type mismatch.

I'm not sure if this matters, but the data that is being pulled will be a mixed bag of upper/lower case, numbers, as well as symbols (eg. '/*>:<&%). I can't see any other commanalities.

First I would try cleaning the data of all non-printable characters.
In Cell B1, type the formula "=Clean(A1)" and copy this down to the
last data row. Then copy and paste column B as values, and then replace
column A with the cleaned-up data in column B.

Then if that still gives the type-mismatch error, you could look for the failure point.
Add this line

Please Login or Register  to view this content.

Insert this line just after the "For j =..." line:

Please Login or Register  to view this content.

Run the code again and see what the value is in column E of your worksheet.
Let's say it reached 935 before the macro fails. Run the macro again and see if
you get the same result in column E. If you do, then put this code just inside the For loop:

Please Login or Register  to view this content.

and set a debug break point on the line "lngTest = lngTest".
Step through the code in debug and identify the line of code that fails and
what the value of the variable j is at that point, which may give some
insight about the offending cell of data.
Stop the macro and run it again to the point where you go into debug, then step
through to the point just before the failure and examine in a Watch window the value
that is in the cell being read. It may be data that does not want to be treated as text.

Try this code that I revised to work with the sample data
in your attachment. Results will be stored in the same sheet as your data,
in column G.

If you get the same type mismatch error and no text appears in column G, put a debug
break point on the line that assigns a value to lastRow. Run the macro and
see if the value for lastRow is the same as the ending data row in column A.

Please Login or Register  to view this content.