| Home |
| Search |
| Today's Posts |
|
#25
Posted to microsoft.public.excel.programming
|
|||
|
|||
runtime error 424
Glad you got it working.
steph wrote: Hi Dave, It worked ! Thank you so much for all you time and effort ! steph "Dave Peterson" schreef: 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. steph wrote: 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: 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? steph wrote: 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: Could it be as simple as .formulaR1C1? If that was just a typo in the message, then try this next. myresultStr = "=SumIf(.... becomes: myresultStr = "SumIf(... Then you're just plopping text into the cell. Back to excel--change to R1C1 Reference style Select one of those cells and insert the leading equal sign. Maybe it'll give you a hint what's wrong with the formula. steph wrote: I'm really sorry, but it still doesn't work. When I'm inserting the myresultStr from the immediate window into excel, I get the formula and result I want. When I'm changing sheets("maaltijdcheques").cells(d,l).formula = myresultStr into sheets("maaltijdcheques").cells(d,l).formula = 4, the 4 is filled in all the defined cells. However when I'm trying back to link sheets("maaltijdcheques").cells(d,l).formula = myresultStr, I receive a runtime error 1004. I really do not see what I'm doing wrong? Can it be something between linken .formula = string which makes it impossible to work ? Steph "Dave Peterson" schreef: The \ operator works in VBA--not in a formula in a cell. Try putting that same formula in that cell manually and see what excel is yelling about. steph wrote: The \, I found in a book explaining VBA, according to the book this should have been an integer split (something like INT(/)) but I think this is not correct. I've corrected the formula as suggested (seems to me a very handy tool, thank you for explaining this possibility) and normally the myresultStr is now the formula/result I want to obtain. The last problem now is that I want to link this formula to a variable cell, I've tried as you suggested : sheets("maaltijdcheques").cells(d,l).FormulaR1C1 = myresultStr but this gives me an runtime error 1004. Anyideas how to solve this ? steph "Dave Peterson" schreef: This looks strange: With Worksheets("globaal uuroverzicht") myresultStr = "=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 What the heck is this ")\ "? Right after you create this myResultStr, add this line: Debug.Print myResultStr Then stop your macro. Show the immediate window and you'll see what the formula (in R1C1 reference style) will look like. Go back to excel and go into R1C1 reference style mode (tools|Options|general tab) Paste your formula into the cell you want and watch excel get mad. Keep track of what you do to fix the formula manually. Then do the same in your code. steph wrote: Hi Dave, It seems that we are living in very different timezones ! Underneath you will find the complete macro. I think you will find very long but this is because I'm still in the figure-out phase of VBA. The macro stops everytime almost at the end. (I'v marked it <==== runtime errorcode 1004 ) <<snipped -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| xpath error? Runtime Error 13 type mismatch | Excel Discussion (Misc queries) | |||
| Excel 2003 Macro Error - Runtime error 1004 | Excel Discussion (Misc queries) | |||
| naming tab macro error runtime error 1004 | Excel Programming | |||
| Interior class error/runtime error 1004 | Excel Programming | |||
| Syntax Error Runtime Error '424' Object Required | Excel Programming | |||
Threaded Mode
