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

Bruce...

I can't exactly understand if these are FORMULAS? They look more like
strings to me..., in which case you'd better use the Cells(x,y).Value
property

Also I suggets you don't do this in a loop.
Once you've got 1 row filled, just use s'thing like


set rngHead=ActiveCell.Resize(1,cCols)
set RgnRest=ActiveCell.Offset(1,0).Resize(cRows-1)
rngHead.Copy rngRest

or

set rngAll=ActiveCell.Resize(cRows,cCols)
rngall.Filldown



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Bruce Roberson" wrote:

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

There is no use looping if you don't change anything in the loop - you don't

If you loop 10 times, for example, you will write the same value to the same
cells 10 times.

Excel will see your first entery as entering a Named Range of FGS~FEE

For me, that causes an error.

I don't know what these formulas do in QPW, but I doubt they are going to
work in Excel.

do these formulas represent a QPW macro?

--
Regards,
Tom Ogilvy






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

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

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

Bruce,

A few thoughts

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.

-- I agree with that approach, it does help. But do you need toi setup formulae or could you just get the
-- values that the formulae represent and plu those in. You only need to load formulae if the data will
-- change after your exercise and you want thw w/s to reflect this.

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)

-- I think FIXED rounds a number to a number of dec places, not convert to a string. TEXT or T

-- functions may be what you want.

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.

-- Amen to that, some thought up front will save lots more time down the line.



Regards

Bob