Actually, whoever keepitcool is was right that some of these are string values rather than formulas and I probably need to show it as a value rather than as a formula in those cases. Its a formula when I get into the lines that have a mix of strings and formulas like fixed, and index. Fixed is the Excel equivalent to @string in QPW and 123, and I've seen it work in Excel to convert a numeric value to a string. =Index I think is a valid Excel formula (guess we'll see Monday when I try it out). In this case below, =Fixed(INDEX(Summary,2,ROWSUM)*Cleanupfeerate,2) just takes the numeric value of the index calculation and turns that into a string. Index(Summary,2,Rowsum) is just extracting a value out of a database range named summary, and it is taking the value in column 2, with the row being whatever the rowsum counter value is at the time of that loop. If rowsum is equal to 3, then the index(summary,2,rowsum) is going to take the value in column 2 of row 3 of that range, and multiply the value in that cell by the value in the range cleanupfeerate. The fixed part just turns that result in a string with two decimal places. So, if the result of all that is 10.25 for that record, then your resulting line would show as:
TIA~8656~~~10.25 (taking the above with TIA 8656 line from below)

That line which is just one line in one record is all garbage to you guys who don't work for the Comptroller of Texas, but to those guys, it means something <LOL It takes about 15 of those types of lines to report one well record in this file format. And, it will loop in this manner for maybe 300 wells. It has headers and footers before the well detail starts. I mistakenly gave some of the header data that looks like stagnant data in the cells example, instead of the looping line values . And that is why some of you guys thought nothing was changing from one looping to the next.

The Comptroller's office calls this an 813 file format. But it is a lot of garbage to most observers. It will be about 4,000 rows worth of this sort of thing and its all in column A. So, this project I am about to convert from QPW to Excel will be quite an excercise by the time I'm done.

I'm glad I gave it some thought this weekend trying to sort out a plan of attack before I actually dive into it.



"Bob Phillips" wrote in message ...

Bruce,

Not much to add to what the other guys have said really, a loop is only required if you are looping (That's the sort of statement that on one level is blindingly obvious, very deep on another <g).

Those ';formulas' look awfully like my recollection of Lotus macros (I'm sure HG could give more meaningful view on this), and I am not sure how Excel handles these. I know it has some type of 123 emulation but I would suggest you review what the formulae are supposed to do and write as Excel formulae.

And ... as your project progresses you know where we are.

--

HTH

Bob Phillips

"Bruce Roberson" wrote in message ...
Bob:

I think I see what you're saying about the cells method. The formulas listed below were just off the wall examples. The 9* whatever was in no way a pattern in these first examples.

activecell.offset(1,0).formula = "=9*1"
activecell.offset(2,0).formula = "=9*2"
activecell.offset(3,0).formula = "=9*3"

I think the real deal with your cells method will go something like the example below. Notice that there is a combination of character expressions mixed in with references to spreadsheet ranges and indexes. So I guess I'll have to see if screenupdating may be necessary in this case to get those spreadsheet range values. But I guess what you're saying in your post is this Cells method is preferable to using the "activecell.offset(X,Y).formula =" approach?

activecell.offset(1,0).formula = "=FGS~FEE"
activecell.offset(2,0).formula = "=N1~PP~~75~0000000"
activecell.offset(3,0).formula = "=N1~C7~~75~000"

As opposed to the real world example here should go something like what is written below:

Application,.Screenupdating = False

For i = 1 To cRows ' will determine how many records to process by a crows approach
Cells(1,0).Formula = "=FGS~FEE"
Cells(2,0).Formula = "=N1~PP~~75~0000000"
Cells(3,0).Formula = "=N1~C7~~75~000"
Cells(4,0).Formula = "=N1~SY~~49~00000000000"
Cells(5,0).Formula = "=TIA~8605~~8"
Cells(6,0).Formula = "=TIA~8020~~0"
Cells(7,0).Formula = "=TIA~8025~~0"
Cells(8,0).Formula = "=TIA~8030~~0"
Cells(9,0).Formula = "=TIA~8030~~0"
Cells(10,0).Formula = "=TIA~8056~~~"&Fixed(INDEX(Summary,2,ROWSUM),0)&"~ FM"
Cells(11,0).Formula = "=TIA~8656~"&Fixed(INDEX(Summary,2,ROWSUM)*Cleanup feerate,2)
Cells(12,0).Formula = "=TIA~8230~~~"&FIXED(INDEX(Summary,2,ROWSUM),0)&"~ FM"
Cells(13,0).Formula = "=TIA~8065~0"
Cells(14,0).Formula ="=TIA~8070~0"
Cells(15,0).Formula ="=TIA~8200~0"
i = i + 1
Next

In QPW, the ~ character was for carriage return. If it was put in a line like the ones above, and you did not want the carriage return at that position, you had to enclose it in braces as such {~}. I am presuming Excel will not be confused in any way about the meaning of the character ~ ?




Bob's example was:

Application,.Screenupdating = False

For i = 1 To 100
Cells(i,1).Formula = "G*" & i
Next

...

Application.ScreenUpdating = True

If you don't know how many rows in advance, use that code I gave you before to determine the number of rows with data, the cRows = Cells(Rows.Count ...

--

HTH

Bob Phillips