Entering a formula in a cell through VBA

[baunataler]

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

 

[JLGWhiz]

");2)"
C;hange to
";2)"

Too many right parentheses.

 

[baunataler]

Dang! Thank you very much JLG,
It worked... and I have been busting my head for the last hours trying to find the problem!

 

[JLGWhiz]

Dang! Thank you very much JLG,
It worked... and I have been busting my head for the last hours trying to find the problem!

Been there, done that
Regards, JLG

 

[offthelip]

The quick way to find these errors when writing a formula using VBA is get rid of the equals sign and write txt to the cell, then go to the cell and edit it manually and put the equals sign back in, excel will then tell you where the error is!!

 

[baunataler]

The quick way to find these errors when writing a formula using VBA is get rid of the equals sign and write txt to the cell, then go to the cell and edit it manually and put the equals sign back in, excel will then tell you where the error is!!

Good tip, thanks, gotta remember that!