Help! Please!

[Kevin]

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

 

[Tom Ogilvy]

I put you code in a procedure:

Sub tester1()
curRow = 40
MaxRow = 51
totcol = 22

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


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


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


End Sub

It ran fine for me. Did just what i would expect it to do. (put two summary
columns on the end of rows 40 to 51 and a sum underneath the first summary
column in row 52.

 

[Kevin]

Can you think of any reason it would shove the formula in
the specified cell down 12 rows? AAAAhhhhh!

 

[Kevin]

Tom,

Thanks for your help!

Kevin

-----Original Message-----
I put you code in a procedure:

Sub tester1()
curRow = 40
MaxRow = 51
totcol = 22

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


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


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


End Sub

It ran fine for me. Did just what i would expect it to do. (put two summary
columns on the end of rows 40 to 51 and a sum underneath the first summary
column in row 52.

--
Regards,
Tom Ogilvy




.

 

[Kevin]

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

 

[Myrna Larson]

Why do you suspect corruption?

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

 

[Tom Ogilvy]

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.

 

[keepITcool]

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 >