baunataler
New Member
- Joined
- Apr 11, 2018
- Messages
- 6
Hello everybody,
I am struggling with the following problem:
I have a spreadsheet with a lot of formulas (roughly 6,000!) which I need to adjust/round. Not an easy task - adjusting one formula then dragging right/down won't work due to the structure of the sheet.
The original formula is =1000000*'Genuine Parts sales (DNP)_F'!$D$16
and the value must be rounded to two decimals, so I would like to have it like =round(1000000*'Genuine Parts sales (DNP)_F'!$D$16,2)
My Excel is in German, so the local would be =runden(1000000*'Genuine Parts sales (DNP)_F'!$D$16;2)
I can read the current formula in each cell, edit it and store it into a variable: strFormula = "=runden(" & Right(Cells(i, 6).Formula, Len(Cells(i, 6).Formula) - 1) & ");2)"
This gives me in degugger the right result (as above), however when I try to insert this value back into the cell through Cells(i,j).formula = strFormula I get a runtime error 1004.
Changing the code to Cells(i,j).FormulaLocal doesn't help either
What am I doing wrong here?
Here is the code:
'reading the formula, adjust and store to variable strFormula
strFormula = "=runden(" & Right(Cells(i, 6).Formula, Len(Cells(i, 6).Formula) - 1) & ");2)"
' insert strFormula back into same cell
' this won't work
Cells(i, j).Formula = strFormula
'this won't work either
Cells(i, j).Formula = "=runden(" & Right(Cells(i, 6).Formula, Len(Cells(i, 6).Formulal) - 1) & ");2)"
' and neither this
Cells(i, j).FormulaLocal = "=runden(" & Right(Cells(i, 6).Formula, Len(Cells(i, 6).Formula) - 1) & ");2)"
Any input is much appreciated.
Thanks a lot and stay safe,
B
I am struggling with the following problem:
I have a spreadsheet with a lot of formulas (roughly 6,000!) which I need to adjust/round. Not an easy task - adjusting one formula then dragging right/down won't work due to the structure of the sheet.
The original formula is =1000000*'Genuine Parts sales (DNP)_F'!$D$16
and the value must be rounded to two decimals, so I would like to have it like =round(1000000*'Genuine Parts sales (DNP)_F'!$D$16,2)
My Excel is in German, so the local would be =runden(1000000*'Genuine Parts sales (DNP)_F'!$D$16;2)
I can read the current formula in each cell, edit it and store it into a variable: strFormula = "=runden(" & Right(Cells(i, 6).Formula, Len(Cells(i, 6).Formula) - 1) & ");2)"
This gives me in degugger the right result (as above), however when I try to insert this value back into the cell through Cells(i,j).formula = strFormula I get a runtime error 1004.
Changing the code to Cells(i,j).FormulaLocal doesn't help either
What am I doing wrong here?
Here is the code:
'reading the formula, adjust and store to variable strFormula
strFormula = "=runden(" & Right(Cells(i, 6).Formula, Len(Cells(i, 6).Formula) - 1) & ");2)"
' insert strFormula back into same cell
' this won't work
Cells(i, j).Formula = strFormula
'this won't work either
Cells(i, j).Formula = "=runden(" & Right(Cells(i, 6).Formula, Len(Cells(i, 6).Formulal) - 1) & ");2)"
' and neither this
Cells(i, j).FormulaLocal = "=runden(" & Right(Cells(i, 6).Formula, Len(Cells(i, 6).Formula) - 1) & ");2)"
Any input is much appreciated.
Thanks a lot and stay safe,
B