runtime error 424

D

Dave Peterson

I don't have a guess.

Excel will try to correct a formula if you're missing stuff (closing parenthesis
for example). You didn't let excel fix the formula for you, did you?
Hi,

Formula was indeed typo must have been .formulaR1C1. when I omit the = in
the myresultStr to myresultStr ="Sumif(...) the formula in text is written in
the cell. When I manually add a = into the cell, the formula works and I get
the result I want to have, so it seems that there is nothing wrong with the
formula itself. But it is still impossible to have the formula with the = in
myresultStr. How can this be ?

"Dave Peterson" schreef:
 
S

steph

No, I didn't. I just added = before the INT(SUMIF("...))"
This is what I have now :
For l = 12 To 23
Set referencepoint = Sheets("maaltijdcheques").Cells(3, l)
Set data = Sheets("data").Cells(15, 2)
With Worksheets("globaal uuroverzicht")
Set myresult = Sheets("globaal uuroverzicht").Range("a1")
myresultStr = "=INT(SUMIF(" & myrange.Address(external:=True,
ReferenceStyle:=xlR1C1) & "; " & referencepoint.Address(external:=True,
ReferenceStyle:=xlR1C1) & ";" & mycolumns.Address(external:=True,
ReferenceStyle:=xlR1C1) & ")/ " & data.Address(external:=True,
ReferenceStyle:=xlR1C1) & " + (1 / 2))"
End With
Debug.Print myresultStr
Sheets("maaltijdcheques").Cells(d, l).FormulaR1C1 = myresultStr
Next





"Dave Peterson" schreef:
 
D

Dave Peterson

VBA is USA centric.

Try changing your semicolons to commas (";" becomes ",").

When VBA passes it back to the cell those list separators to what your locale
needs.


No, I didn't. I just added = before the INT(SUMIF("...))"
This is what I have now :
For l = 12 To 23
Set referencepoint = Sheets("maaltijdcheques").Cells(3, l)
Set data = Sheets("data").Cells(15, 2)
With Worksheets("globaal uuroverzicht")
Set myresult = Sheets("globaal uuroverzicht").Range("a1")
myresultStr = "=INT(SUMIF(" & myrange.Address(external:=True,
ReferenceStyle:=xlR1C1) & "; " & referencepoint.Address(external:=True,
ReferenceStyle:=xlR1C1) & ";" & mycolumns.Address(external:=True,
ReferenceStyle:=xlR1C1) & ")/ " & data.Address(external:=True,
ReferenceStyle:=xlR1C1) & " + (1 / 2))"
End With
Debug.Print myresultStr
Sheets("maaltijdcheques").Cells(d, l).FormulaR1C1 = myresultStr
Next

"Dave Peterson" schreef:
 
S

steph

Hi Dave,

It worked ! Thank you so much for all you time and effort !

steph

"Dave Peterson" schreef:
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top