K
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
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