PDA

View Full Version : Formula from VBA doesn't work



frankgtl
10-07-2007, 04:04 AM
This is my first post to this forum - sorry that I placed it in the wrong place the first time...
**********************************************

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).Value = 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

unmarkedhelicopter
10-07-2007, 04:19 AM
Can you post an example workbook, with your code, a text entry formula and a working formula ?

Bob Phillips
10-07-2007, 04:51 AM
vAktivCelle = "J" & i
vFormel = "=if(E" & i & "=0;if((G" & i & "+H" & i & ")*Parametre!$E$5" & _
"+G" & i & "+H" & i & "+I" & i & "-F" & i & "<0;0;" & _
"(G" & i & "+H" & i & ")*Parametre!$E$5+G" & i & "+H" & i & "+I" & i & "-F" & i & ");" & _
"if(G" & i & "+H" & i & "+I" & i & "-F" & i & "<0;0;" & _
"if(E" & i & "=F" & i & ";0;G" & i & "+H" & i & "+I" & i & "-F" & i & ")))"
ActiveSheet.Range(vAktivCelle).Value = vFormel

Norie
10-07-2007, 06:00 AM
Use the comma seperator in the formula not the semicolon.

vFormel = "=IF(E" & I & "=0,if((G" & I & "+H" & I & ")*Parametre!$E$5" & _
"+G" & I & "+H" & I & "+I" & I & "-F" & I & "<0,0," & _
"(G" & I & "+H" & I & ")*Parametre!$E$5+G" & I & "+H" & I & "+I" & I & "-F" & I & ")," & _
"IF(G" & I & "+H" & I & "+I" & I & "-F" & I & "<0,0," & _
"IF(E" & I & "=F" & I & "0,G" & I & "+H" & I & "+I" & I & "-F" & I & ")))"

frankgtl
10-07-2007, 01:23 PM
Excel is Norwegian version - and we use ; as separator in functions.

frankgtl
10-07-2007, 01:26 PM
thanks for replying xld... but it doesn't work.

mdmackillop
10-07-2007, 01:37 PM
Try
ActiveSheet.Range(vAktivCelle).Formula = vFormel
(or the Norwegian equivalent)

Bob Phillips
10-07-2007, 02:33 PM
thanks for replying xld... but it doesn't work.

Worked for me (wit comma separators of course).

johnske
10-07-2007, 06:47 PM
This alternative example also generates the same error, but when I replace all the semi-colons (;) with commas (,) it works fine :) EDIT: < OOPS

Range("J1:J12").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)))"
Range("J1:J12").FillDown '< change range to suit


alternatively,

Range("J1:J12").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)))"