|
Alternative to using .Select for cursor movement.
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 |
Alternative to using .Select for cursor movement.
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 |
Alternative to using .Select for cursor movement.
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 |
Alternative to using .Select for cursor movement.
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 |
Alternative to using .Select for cursor movement.
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 |
Alternative to using .Select for cursor movement.
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 |
| All times are GMT +1. The time now is 01:27 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com