Excel formula doesn't work when put in from VBA, but works when written in Excel

[Frank_T_L]

I have made vba-code that makes a formula to be pasted into one cell at the
time in Excel.

The vba-code:
vAktivCelle = "J" & i
vFormel = "=if(E" & i & "=0;if(((G" & i & "+H" & i &
")*Parametre!$E$5"
vFormel = vFormel & "+G" & i & "+H" & i & "+I" & i & ")-F" & i &
"<0;0;"
vFormel = vFormel & "(G" & i & "+H" & i & ")*Parametre!$E$5+G" & i &
"+H" & i & "+I" & i & "-F" & i & ");"
vFormel = vFormel & "if(G" & i & "+H" & i & "+I" & i & "-F" & i &
"<0;0;"
vFormel = vFormel & "if(E" & i & "=F" & i & ";0;G" & i & "+H" & i &
"+I" & i & "-F" & i & ")))"
ActiveSheet.Range(vAktivCelle).Formula = vFormel

This generates this formula:
=if(E2=0;if(((G2+H2)*Parametre!$E$5+G2+H2+I2)-F2<0;0;(G2+H2)*Parametre!$E$5+G2+H2+I2-F2);if(G2+H2+I2-F2<0;0;if(E2=F2;0;G2+H2+I2-F2)))

When I run it - I get this error-message:
Run-time error '1004'
Application-defined or object-defines error

**************

But: if I remove the first "=", and vba just puts the text into the cells -
I can just go to the cell, write "=" in front of the text - and then IT
WORKS....!

Anyone got an idea about why it doesn't work when I put it in from vba?

Frank

 

[Dave Peterson]

Excel is USA centric.

Try changing your semicolons ( to commas (,).

 

[Niek Otten]

You can also look at the FormulaLocal option; it uses local separators and function names

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Excel is USA centric.
|
| Try changing your semicolons ( to commas (,).
|
|
|
| Frank_T_L wrote:
| >
| > I have made vba-code that makes a formula to be pasted into one cell at the
| > time in Excel.
| >
| > The vba-code:
| > vAktivCelle = "J" & i
| > vFormel = "=if(E" & i & "=0;if(((G" & i & "+H" & i &
| > ")*Parametre!$E$5"
| > vFormel = vFormel & "+G" & i & "+H" & i & "+I" & i & ")-F" & i &
| > "<0;0;"
| > vFormel = vFormel & "(G" & i & "+H" & i & ")*Parametre!$E$5+G" & i &
| > "+H" & i & "+I" & i & "-F" & i & ");"
| > vFormel = vFormel & "if(G" & i & "+H" & i & "+I" & i & "-F" & i &
| > "<0;0;"
| > vFormel = vFormel & "if(E" & i & "=F" & i & ";0;G" & i & "+H" & i &
| > "+I" & i & "-F" & i & ")))"
| > ActiveSheet.Range(vAktivCelle).Formula = vFormel
| >
| > This generates this formula:
| >
=if(E2=0;if(((G2+H2)*Parametre!$E$5+G2+H2+I2)-F2<0;0;(G2+H2)*Parametre!$E$5+G2+H2+I2-F2);if(G2+H2+I2-F2<0;0;if(E2=F2;0;G2+H2+I2-F2)))
| >
| > When I run it - I get this error-message:
| > Run-time error '1004'
| > Application-defined or object-defines error
| >
| > **************
| >
| > But: if I remove the first "=", and vba just puts the text into the cells -
| > I can just go to the cell, write "=" in front of the text - and then IT
| > WORKS....!
| >
| > Anyone got an idea about why it doesn't work when I put it in from vba?
| >
| > Frank
|
| --
|
| Dave Peterson