M
mp
I have a vba routine that enters formulas in two cells
In one cell the formula appears correctly (you see the formula in the
Formula bar when cursor is in cell) - this formula is a Sum formula
The cell in the spreadsheet displays the value of course, but you can see
the formula when you enter the cell.
The other cell only shows the value, as if the cell were converted from
formula to value
This formula is a division of one cell by another (or sum of multiple cells)
So the cell displays the value, but the Formula bar also displays the value
and not the formula which leads to the value. (As if I had converted
formulas to values)
I see no difference between the lines that compose the text string to enter
the formula in the two cells.
here are the two lines(occurring in a loop that increments rows) that
compose the formula strings
Dim sFormula as String
sFormula = "=Sum(" & ColAmt & CStr(RowStart) & ":" & ColAmt & CStr(RowGain -
1) & ")"
Dim sFormulaPct as String
sFormulaPct = "=" & ColGain & CStr(RowGain) & "/Sum(" & ColAmt &
CStr(RowCostFirst) & ":" & ColAmt & CStr(RowCostLast) & ")"
I can see the correct data being composed by those strcats in the immediate
window
sFormula: =Sum(K109:K110) <---this appears in formula bar correctly
sFormulaPct: =M111/Sum(K110:K110) <----this doesn't appear in formula
bar...gets converted to it's value
then I enter the formulas in the target cells
With oThisSheet
..Cells(RowGain, ColGain).Value = sFormula
..Cells(RowGain, ColPct).Value = sFormulaPct
End With
I also tried
..Cells(RowGain, ColPct).Formula = sFormulaPct
but that didn't make any difference
so cell N111 should be "=M111/Sum(K110:K110)" when you enter the cell and
look in the address bar
but instead it reads -1.94175724299356%
what am I doing wrong
The column for Pct is formated as percent but I also tried formating as
General before running routine and that doesn't help.
Thanks
Mark
In one cell the formula appears correctly (you see the formula in the
Formula bar when cursor is in cell) - this formula is a Sum formula
The cell in the spreadsheet displays the value of course, but you can see
the formula when you enter the cell.
The other cell only shows the value, as if the cell were converted from
formula to value
This formula is a division of one cell by another (or sum of multiple cells)
So the cell displays the value, but the Formula bar also displays the value
and not the formula which leads to the value. (As if I had converted
formulas to values)
I see no difference between the lines that compose the text string to enter
the formula in the two cells.
here are the two lines(occurring in a loop that increments rows) that
compose the formula strings
Dim sFormula as String
sFormula = "=Sum(" & ColAmt & CStr(RowStart) & ":" & ColAmt & CStr(RowGain -
1) & ")"
Dim sFormulaPct as String
sFormulaPct = "=" & ColGain & CStr(RowGain) & "/Sum(" & ColAmt &
CStr(RowCostFirst) & ":" & ColAmt & CStr(RowCostLast) & ")"
I can see the correct data being composed by those strcats in the immediate
window
sFormula: =Sum(K109:K110) <---this appears in formula bar correctly
sFormulaPct: =M111/Sum(K110:K110) <----this doesn't appear in formula
bar...gets converted to it's value
then I enter the formulas in the target cells
With oThisSheet
..Cells(RowGain, ColGain).Value = sFormula
..Cells(RowGain, ColPct).Value = sFormulaPct
End With
I also tried
..Cells(RowGain, ColPct).Formula = sFormulaPct
but that didn't make any difference
so cell N111 should be "=M111/Sum(K110:K110)" when you enter the cell and
look in the address bar
but instead it reads -1.94175724299356%
what am I doing wrong
The column for Pct is formated as percent but I also tried formating as
General before running routine and that doesn't help.
Thanks
Mark
