Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help! Please! - - - PLEASEEEEEE!
I fear file corruption and am trying to recreate
spreadsheet file which is a somewhat (with extreme sarcasm noted!)painful process. I have probably 4000 to 5000 lines of code 2 user forms and three worksheets. Ahhh! If anyone suggest anything else, I will be open to suggestions. I expect this will take several hours, so you have time to contemplate my predicament. Any suggestions, other than recreate the file will be greatly appreciated!!!!!!!!! I tried exporting all the worksheet, forms and module code and then recreated the worksheet itself with layouts, data, equations, etc by importing these parts of the file into a new workbook. This did not work. Kevin -----Original Message----- I posted this question yesterday but did not receive a reply. I am really confused by this problem and need some help! I have a macro which does several things in preparation for printing the worksheet. First it performs a few calculations on data entered into the worksheet by the user. It then places the result of these calculations in the first empty column on the appropriate row. My spreadsheet is 22 columns wide, but the user typically will not use that many. Each column consitutes a record. The calculations are as follows: 1. Each row of data starting at row 40 from column 1 to the last column with data entered by the user is summed across the row. The calculated data in then inserted into the next available column (containing now user entered data). 2. Percentages are then calculated on the summed data and inserted into the next available column. My code is as follows: 'the following loop sums each row and inserts the data into the next available column (totCol+1) Do While curRow <= maxRow Set sumrng = Range(Cells(curRow, 2), Cells(curRow, totCol)) Sumtot= Application.WorksheetFunction.Sum(sumrng) Worksheets(1).Cells(curRow, totCol+ 1).Value2 = "" Worksheets(1).Cells(curRow, totCol+ 1).Value2 = Sumtot curRow = curRow + 1 Loop 'the following code sums the total of column totCol+1 and inserts the sum into row 52 in column totCol+1 curRow = 40 Set sumrng = Range(Cells(curRow, totCol+ 1), Cells (maxRow, totCol+ 1)) SumTot = Application.WorksheetFunction.Sum(sumrng) Worksheets(1).Cells(52, totCol+ 1).Value2 = SumTot cntnrTTL = Worksheets(1).Cells(52, totCol+ 1).Value2 curRow = 40 'the following loop calculates the average percent of each row then puts the calculated value in the appropriate row Do While curRow <= maxRow mtrTTL = Worksheets(1).Cells(curRow, totCol+ 1).Value2 avgMtPct = (mtrTTL / cntnrTTL) * 100 Worksheets(1).Cells(curRow, totCol+ 2).Value2 = avgMtPct curRow = curRow + 1 Loop Each column in the affected rows (when this code runs) has a formula in the cell. There are 12 rows where this code runs. When the code runs, the formula in the current cell the code is working with is moved down 12 rows and the new calculated data is inserted into the cell. I only want the formula in the cell to be replaced, not moved. The affect this has is to cause a #Value! error to be displayed in cell where the formula is moved. This is because there is no data in the cells referenced by the formula (because the user did not enter data into that column). How do I stop this formula from being moved and just replace the formula?!?! HELP PLEASE Kevin . |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help! Please! - - - PLEASEEEEEE!
Why do you suspect corruption?
On Fri, 8 Oct 2004 11:04:06 -0700, "Kevin" wrote: I fear file corruption and am trying to recreate spreadsheet file which is a somewhat (with extreme sarcasm noted!)painful process. I have probably 4000 to 5000 lines of code 2 user forms and three worksheets. Ahhh! If anyone suggest anything else, I will be open to suggestions. I expect this will take several hours, so you have time to contemplate my predicament. Any suggestions, other than recreate the file will be greatly appreciated!!!!!!!!! I tried exporting all the worksheet, forms and module code and then recreated the worksheet itself with layouts, data, equations, etc by importing these parts of the file into a new workbook. This did not work. Kevin -----Original Message----- I posted this question yesterday but did not receive a reply. I am really confused by this problem and need some help! I have a macro which does several things in preparation for printing the worksheet. First it performs a few calculations on data entered into the worksheet by the user. It then places the result of these calculations in the first empty column on the appropriate row. My spreadsheet is 22 columns wide, but the user typically will not use that many. Each column consitutes a record. The calculations are as follows: 1. Each row of data starting at row 40 from column 1 to the last column with data entered by the user is summed across the row. The calculated data in then inserted into the next available column (containing now user entered data). 2. Percentages are then calculated on the summed data and inserted into the next available column. My code is as follows: 'the following loop sums each row and inserts the data into the next available column (totCol+1) Do While curRow <= maxRow Set sumrng = Range(Cells(curRow, 2), Cells(curRow, totCol)) Sumtot= Application.WorksheetFunction.Sum(sumrng) Worksheets(1).Cells(curRow, totCol+ 1).Value2 = "" Worksheets(1).Cells(curRow, totCol+ 1).Value2 = Sumtot curRow = curRow + 1 Loop 'the following code sums the total of column totCol+1 and inserts the sum into row 52 in column totCol+1 curRow = 40 Set sumrng = Range(Cells(curRow, totCol+ 1), Cells (maxRow, totCol+ 1)) SumTot = Application.WorksheetFunction.Sum(sumrng) Worksheets(1).Cells(52, totCol+ 1).Value2 = SumTot cntnrTTL = Worksheets(1).Cells(52, totCol+ 1).Value2 curRow = 40 'the following loop calculates the average percent of each row then puts the calculated value in the appropriate row Do While curRow <= maxRow mtrTTL = Worksheets(1).Cells(curRow, totCol+ 1).Value2 avgMtPct = (mtrTTL / cntnrTTL) * 100 Worksheets(1).Cells(curRow, totCol+ 2).Value2 = avgMtPct curRow = curRow + 1 Loop Each column in the affected rows (when this code runs) has a formula in the cell. There are 12 rows where this code runs. When the code runs, the formula in the current cell the code is working with is moved down 12 rows and the new calculated data is inserted into the cell. I only want the formula in the cell to be replaced, not moved. The affect this has is to cause a #Value! error to be displayed in cell where the formula is moved. This is because there is no data in the cells referenced by the formula (because the user did not enter data into that column). How do I stop this formula from being moved and just replace the formula?!?! HELP PLEASE Kevin . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help! Please! - - - PLEASEEEEEE!
Your code doesn't push anything down. If it is happening on your worksheet,
it doesn't appear to be done by any of the code you posted. Given that, there is not much anyone can off in the way of help. -- Regards, Tom Ogilvy "Kevin" wrote in message ... I fear file corruption and am trying to recreate spreadsheet file which is a somewhat (with extreme sarcasm noted!)painful process. I have probably 4000 to 5000 lines of code 2 user forms and three worksheets. Ahhh! If anyone suggest anything else, I will be open to suggestions. I expect this will take several hours, so you have time to contemplate my predicament. Any suggestions, other than recreate the file will be greatly appreciated!!!!!!!!! I tried exporting all the worksheet, forms and module code and then recreated the worksheet itself with layouts, data, equations, etc by importing these parts of the file into a new workbook. This did not work. Kevin -----Original Message----- I posted this question yesterday but did not receive a reply. I am really confused by this problem and need some help! I have a macro which does several things in preparation for printing the worksheet. First it performs a few calculations on data entered into the worksheet by the user. It then places the result of these calculations in the first empty column on the appropriate row. My spreadsheet is 22 columns wide, but the user typically will not use that many. Each column consitutes a record. The calculations are as follows: 1. Each row of data starting at row 40 from column 1 to the last column with data entered by the user is summed across the row. The calculated data in then inserted into the next available column (containing now user entered data). 2. Percentages are then calculated on the summed data and inserted into the next available column. My code is as follows: 'the following loop sums each row and inserts the data into the next available column (totCol+1) Do While curRow <= maxRow Set sumrng = Range(Cells(curRow, 2), Cells(curRow, totCol)) Sumtot= Application.WorksheetFunction.Sum(sumrng) Worksheets(1).Cells(curRow, totCol+ 1).Value2 = "" Worksheets(1).Cells(curRow, totCol+ 1).Value2 = Sumtot curRow = curRow + 1 Loop 'the following code sums the total of column totCol+1 and inserts the sum into row 52 in column totCol+1 curRow = 40 Set sumrng = Range(Cells(curRow, totCol+ 1), Cells (maxRow, totCol+ 1)) SumTot = Application.WorksheetFunction.Sum(sumrng) Worksheets(1).Cells(52, totCol+ 1).Value2 = SumTot cntnrTTL = Worksheets(1).Cells(52, totCol+ 1).Value2 curRow = 40 'the following loop calculates the average percent of each row then puts the calculated value in the appropriate row Do While curRow <= maxRow mtrTTL = Worksheets(1).Cells(curRow, totCol+ 1).Value2 avgMtPct = (mtrTTL / cntnrTTL) * 100 Worksheets(1).Cells(curRow, totCol+ 2).Value2 = avgMtPct curRow = curRow + 1 Loop Each column in the affected rows (when this code runs) has a formula in the cell. There are 12 rows where this code runs. When the code runs, the formula in the current cell the code is working with is moved down 12 rows and the new calculated data is inserted into the cell. I only want the formula in the cell to be replaced, not moved. The affect this has is to cause a #Value! error to be displayed in cell where the formula is moved. This is because there is no data in the cells referenced by the formula (because the user did not enter data into that column). How do I stop this formula from being moved and just replace the formula?!?! HELP PLEASE Kevin . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help! Please! - - - PLEASEEEEEE!
only thing what i can see..
is that the first part works on the active sheet while further down it works on worksheets(1) keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool "Tom Ogilvy" wrote: Your code doesn't push anything down. If it is happening on your worksheet, it doesn't appear to be done by any of the code you posted. Given that, there is not much anyone can off in the way of help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|