Trouble scaling formulas in template to process test data.

[NFrank89]

Hello!

I currently have a workbook I use to process test logs.
You paste the log into cell A7 and the Macro button does the rest.
The last 3 columns of each row has a formula that looks for data
in adjacent cells and pulls corresponding data from another sheet
into the last column.

My sheet is setup with headers in row 5
_ A _ B _ C _ D _ E _ F _
1|
2|
3|
4|
5| Date |Time |Event |Error |Pram |Description
6| Paste V | | |Form1 | Form2 | Form3
7| Log Data| | | | |


My macro button just takes my space-deliminated log file (Pasted in A7)
Converts text to columns (via text to columns tool)
Then "double clicks" the corner of the marquee in cells D6 E6 F6 to copy
my formulas down until the end of the data range.

I activate the macro after data has been pasted in A7 but the length of
the file changes every time. My macro only copies the formula down
438 rows (the length of the file I used to setup the template) but if a log
happens to be longer than that, the formulas stop at row 438 and I have
to manually copy the formulas down (double click marquee)

My workaround was to preload these cells with the correct formula
allllllll the way down the sheet. This left me with a 25MB worksheet that
takes forever to load.

I believe what I need is for the formulas to only be added to the last 3
cells of a row if there is data in the first cell. I'm not really sure how to
set this up though.

-----

After thinking a little I came up with this but I'm not exactly sure what formula(s)
will achieve this result so maybe you guys can help me convert this concept into
a working formula. unless you can think of a better way to achieve the same results.

I'm thinking that when I record my macro, after text to columns, paste a formula into
column G6 that checks A7 for contents, if it has something there then it copies the
formulas from D6 E6 F6 and G6 into that row.

Having the G6 formula copied to G7 now, It check's line A8 and if it contains
data it copies formulas into D8 E8 F8 and G8.

This process repeats until the formula in the last G cell does not see contents
in the A cell below it (aka the end of the log file!)

Any feedback is greatly appreciated!

[MrShorty]

Would you consider a different maybe even non-macro approach? When I do something similar, I use the Data -> "Get External Data" -> "From text" command.
1) This command first brings up the file navigation window where you select the file.
2) Then you get the "Text Import Wizard", just like the Text to Columns command.
3) Then you specify where you want the data placed. As part of this dialog is a "Properties" button. In this dialog you can.
a) Specify whether or not to save the import definition
b) Control "refresh" options (prompt for filename, refresh manually or at time intervals, etc.)
c) Control options if the number of rows changes when the data range refreshes.
d) and have Excel fill formulas in the columns immediately adjacent to the data range.

I have found this built in utility in Excel to be very useful when processing and analyzing my own space delimited text files, usually preferring this built in option to using macros.

[nigelbloomy]

Can you paste you macro with code tags around it? It looks like you would just need to let the macro figure out where the bottom row is. That is pretty easy to add in with something like this.

Please Login or Register  to view this content.