X
Xman019
Hey folks,
I'm hoping someone can help me figure this out as I've been searching help
files and different blog sites trying to find a macro for this...
I have a text file that I am converting to Excel, but it defaults to having
all of the info in Column A. I have no problem delimiting it, but the only
way I can delimit it reliably is by breaking it up at every space. There are
headers built in that I need to search for and then take the info from the
cell directly to the right of the header once i find it and put it into the
new spreadsheet.
If the report was formatted right I could just use some 'if-then' type of
macro's using R1C1 formulas to do this, but the problem is that this report
runs between 2000-3000 rows, where one record on the report could be anywhere
from 6 to 14 rows, and to complicate it further, if there are multiple
records for the same account it lists the account level information (such as
account holder name and account number) once, then lists all of the records
for that account following the header. (each record is seperated by a blank
row)
The last complication is that the report i'm converting is really set up to
be printed rather than put into an excel file, so where each printed page
would end, it stops the record it's on, inserts a header for the next page
which includes the same account level information and continues the record it
was printing.
I've got a macro to take all of the page headers out, and I have the coding
I need to handle the problem with dropping the account number down for each
record already.
I THINK I need a macro to go in and look at column B, then set up a lookup
table for each record. All of the records start with a cell (in column B)
that has the text value of "REF:". I'm thinking if I could come up with a
macro to have it set the lookup range for each record from the row that
contains REF to the last row before the next REF, from columns B - N, I could
then have it find each header I'm looking for using vlookup and then copy the
cell to the right of it to the new sheet.
I know it's asking a lot, but if you're taken the time to read this whole
post, I'm guessing you may be willing to help out... Does anyone have at
least any pointers to give me that might get me at least in the right
neighborhood for this?
I'm hoping someone can help me figure this out as I've been searching help
files and different blog sites trying to find a macro for this...
I have a text file that I am converting to Excel, but it defaults to having
all of the info in Column A. I have no problem delimiting it, but the only
way I can delimit it reliably is by breaking it up at every space. There are
headers built in that I need to search for and then take the info from the
cell directly to the right of the header once i find it and put it into the
new spreadsheet.
If the report was formatted right I could just use some 'if-then' type of
macro's using R1C1 formulas to do this, but the problem is that this report
runs between 2000-3000 rows, where one record on the report could be anywhere
from 6 to 14 rows, and to complicate it further, if there are multiple
records for the same account it lists the account level information (such as
account holder name and account number) once, then lists all of the records
for that account following the header. (each record is seperated by a blank
row)
The last complication is that the report i'm converting is really set up to
be printed rather than put into an excel file, so where each printed page
would end, it stops the record it's on, inserts a header for the next page
which includes the same account level information and continues the record it
was printing.
I've got a macro to take all of the page headers out, and I have the coding
I need to handle the problem with dropping the account number down for each
record already.
I THINK I need a macro to go in and look at column B, then set up a lookup
table for each record. All of the records start with a cell (in column B)
that has the text value of "REF:". I'm thinking if I could come up with a
macro to have it set the lookup range for each record from the row that
contains REF to the last row before the next REF, from columns B - N, I could
then have it find each header I'm looking for using vlookup and then copy the
cell to the right of it to the new sheet.
I know it's asking a lot, but if you're taken the time to read this whole
post, I'm guessing you may be willing to help out... Does anyone have at
least any pointers to give me that might get me at least in the right
neighborhood for this?