|
runtime error 424
Hi,
I'm having problems defining my sumif-formula. It returns a runtime error 424 and I don't know how to solve this problem. The formula is searching in a dynamic range and has to sum the values in a number of specific columns when the condition is met. Enclosed you will find the part of the macro which is returning the error. Sub cmdok_click() Dim rij As Integer Dim myrange As Range Dim myselection As Range Dim mycolumns As Range Dim referencepoint As Range For l = 12 To 23 Set referencepoint = Sheets("maaltijdcheques").Cells(3, l) With Worksheets("globaal uuroverzicht") Set mycolumns = .Range("V:AB") End With With Worksheets("globaal uuroverzicht") Set myresult.FormulaR1C1 = Application.WorksheetFunction.SumIf(myrange, " & referencepoint.Address(external:=True, ReferenceStyle:=xlR1C1) & ", mycolumns) End With Cells(d, l).Formula = myresult / (Worksheets("data").R15C2 + (1 / 2)) Next many thanks ! steph |
runtime error 424
I could be wrong but I don't see where you have defined "myrange". That
might be it. "steph" wrote: Hi, I'm having problems defining my sumif-formula. It returns a runtime error 424 and I don't know how to solve this problem. The formula is searching in a dynamic range and has to sum the values in a number of specific columns when the condition is met. Enclosed you will find the part of the macro which is returning the error. Sub cmdok_click() Dim rij As Integer Dim myrange As Range Dim myselection As Range Dim mycolumns As Range Dim referencepoint As Range For l = 12 To 23 Set referencepoint = Sheets("maaltijdcheques").Cells(3, l) With Worksheets("globaal uuroverzicht") Set mycolumns = .Range("V:AB") End With With Worksheets("globaal uuroverzicht") Set myresult.FormulaR1C1 = Application.WorksheetFunction.SumIf(myrange, " & referencepoint.Address(external:=True, ReferenceStyle:=xlR1C1) & ", mycolumns) End With Cells(d, l).Formula = myresult / (Worksheets("data").R15C2 + (1 / 2)) Next many thanks ! steph |
runtime error 424
You declared myRange and used myResult--but never set them to anything.
And when you're assigning a string to that .formular1c1, the you shouldn't use "Set". And I would think that you would want something like: myResult.FormulaR1C1 = "=sumif(" & _ myrange.Address(external:=True, ReferenceStyle:=xlR1C1) _ & "," _ & referencepoint.Address(external:=True, ReferenceStyle:=xlR1C1) _ & "," & mycolumns.Address(external:=True, ReferenceStyle:=xlR1C1) _ & ")" If you're creating the formula, you want to build the string that looks like a formula. If you wanted the code to evaluate that condition, then you'd use application.worksheetfunction.sumif(). steph wrote: Hi, I'm having problems defining my sumif-formula. It returns a runtime error 424 and I don't know how to solve this problem. The formula is searching in a dynamic range and has to sum the values in a number of specific columns when the condition is met. Enclosed you will find the part of the macro which is returning the error. Sub cmdok_click() Dim rij As Integer Dim myrange As Range Dim myselection As Range Dim mycolumns As Range Dim referencepoint As Range For l = 12 To 23 Set referencepoint = Sheets("maaltijdcheques").Cells(3, l) With Worksheets("globaal uuroverzicht") Set mycolumns = .Range("V:AB") End With With Worksheets("globaal uuroverzicht") Set myresult.FormulaR1C1 = Application.WorksheetFunction.SumIf(myrange, " & referencepoint.Address(external:=True, ReferenceStyle:=xlR1C1) & ", mycolumns) End With Cells(d, l).Formula = myresult / (Worksheets("data").R15C2 + (1 / 2)) Next many thanks ! steph -- Dave Peterson |
runtime error 424
Hi Dave
The myrange has been defined and used in another part of the macro. When adapting the formula to yours, i receive another errorcode being runtime error 91 (object variable or with block variable not set). I've defined myresult as range and I have omitted the with ... end with. However when I include again the with ... end with, i still get the same errorcode. "Dave Peterson" schreef: You declared myRange and used myResult--but never set them to anything. And when you're assigning a string to that .formular1c1, the you shouldn't use "Set". And I would think that you would want something like: myResult.FormulaR1C1 = "=sumif(" & _ myrange.Address(external:=True, ReferenceStyle:=xlR1C1) _ & "," _ & referencepoint.Address(external:=True, ReferenceStyle:=xlR1C1) _ & "," & mycolumns.Address(external:=True, ReferenceStyle:=xlR1C1) _ & ")" If you're creating the formula, you want to build the string that looks like a formula. If you wanted the code to evaluate that condition, then you'd use application.worksheetfunction.sumif(). steph wrote: Hi, I'm having problems defining my sumif-formula. It returns a runtime error 424 and I don't know how to solve this problem. The formula is searching in a dynamic range and has to sum the values in a number of specific columns when the condition is met. Enclosed you will find the part of the macro which is returning the error. Sub cmdok_click() Dim rij As Integer Dim myrange As Range Dim myselection As Range Dim mycolumns As Range Dim referencepoint As Range For l = 12 To 23 Set referencepoint = Sheets("maaltijdcheques").Cells(3, l) With Worksheets("globaal uuroverzicht") Set mycolumns = .Range("V:AB") End With With Worksheets("globaal uuroverzicht") Set myresult.FormulaR1C1 = Application.WorksheetFunction.SumIf(myrange, " & referencepoint.Address(external:=True, ReferenceStyle:=xlR1C1) & ", mycolumns) End With Cells(d, l).Formula = myresult / (Worksheets("data").R15C2 + (1 / 2)) Next many thanks ! steph -- Dave Peterson |
runtime error 424
I think I'd add some msgboxes to see what's happening:
msgbox myrange.address(external:=true) msgbox referencepoint.address(external:=true) msgbox mycolumns.address(external:=true) msgbox myresult.address(external:=true) I'm guessing that one of these variables isn't set to a real range. steph wrote: Hi Dave The myrange has been defined and used in another part of the macro. When adapting the formula to yours, i receive another errorcode being runtime error 91 (object variable or with block variable not set). I've defined myresult as range and I have omitted the with ... end with. However when I include again the with ... end with, i still get the same errorcode. "Dave Peterson" schreef: You declared myRange and used myResult--but never set them to anything. And when you're assigning a string to that .formular1c1, the you shouldn't use "Set". And I would think that you would want something like: myResult.FormulaR1C1 = "=sumif(" & _ myrange.Address(external:=True, ReferenceStyle:=xlR1C1) _ & "," _ & referencepoint.Address(external:=True, ReferenceStyle:=xlR1C1) _ & "," & mycolumns.Address(external:=True, ReferenceStyle:=xlR1C1) _ & ")" If you're creating the formula, you want to build the string that looks like a formula. If you wanted the code to evaluate that condition, then you'd use application.worksheetfunction.sumif(). steph wrote: Hi, I'm having problems defining my sumif-formula. It returns a runtime error 424 and I don't know how to solve this problem. The formula is searching in a dynamic range and has to sum the values in a number of specific columns when the condition is met. Enclosed you will find the part of the macro which is returning the error. Sub cmdok_click() Dim rij As Integer Dim myrange As Range Dim myselection As Range Dim mycolumns As Range Dim referencepoint As Range For l = 12 To 23 Set referencepoint = Sheets("maaltijdcheques").Cells(3, l) With Worksheets("globaal uuroverzicht") Set mycolumns = .Range("V:AB") End With With Worksheets("globaal uuroverzicht") Set myresult.FormulaR1C1 = Application.WorksheetFunction.SumIf(myrange, " & referencepoint.Address(external:=True, ReferenceStyle:=xlR1C1) & ", mycolumns) End With Cells(d, l).Formula = myresult / (Worksheets("data").R15C2 + (1 / 2)) Next many thanks ! steph -- Dave Peterson -- Dave Peterson |
runtime error 424
Hi,
The messageboxes show that my defined ranges are well set. referencepoint =[test.xls]maaltijdcheques!$L$3 myrange = [test.xls]globaal uuroverzicht!$B$565:$B$577 mycolumns = [test.xls]globaal uuroverzicht!$V$565:$AB$577 but for myresult is still get the runtime errorcode 91 Anymore thoughts of what could be wrong Thanks stephanie "Dave Peterson" schreef: I think I'd add some msgboxes to see what's happening: msgbox myrange.address(external:=true) msgbox referencepoint.address(external:=true) msgbox mycolumns.address(external:=true) msgbox myresult.address(external:=true) I'm guessing that one of these variables isn't set to a real range. steph wrote: Hi Dave The myrange has been defined and used in another part of the macro. When adapting the formula to yours, i receive another errorcode being runtime error 91 (object variable or with block variable not set). I've defined myresult as range and I have omitted the with ... end with. However when I include again the with ... end with, i still get the same errorcode. "Dave Peterson" schreef: You declared myRange and used myResult--but never set them to anything. And when you're assigning a string to that .formular1c1, the you shouldn't use "Set". And I would think that you would want something like: myResult.FormulaR1C1 = "=sumif(" & _ myrange.Address(external:=True, ReferenceStyle:=xlR1C1) _ & "," _ & referencepoint.Address(external:=True, ReferenceStyle:=xlR1C1) _ & "," & mycolumns.Address(external:=True, ReferenceStyle:=xlR1C1) _ & ")" If you're creating the formula, you want to build the string that looks like a formula. If you wanted the code to evaluate that condition, then you'd use application.worksheetfunction.sumif(). steph wrote: Hi, I'm having problems defining my sumif-formula. It returns a runtime error 424 and I don't know how to solve this problem. The formula is searching in a dynamic range and has to sum the values in a number of specific columns when the condition is met. Enclosed you will find the part of the macro which is returning the error. Sub cmdok_click() Dim rij As Integer Dim myrange As Range Dim myselection As Range Dim mycolumns As Range Dim referencepoint As Range For l = 12 To 23 Set referencepoint = Sheets("maaltijdcheques").Cells(3, l) With Worksheets("globaal uuroverzicht") Set mycolumns = .Range("V:AB") End With With Worksheets("globaal uuroverzicht") Set myresult.FormulaR1C1 = Application.WorksheetFunction.SumIf(myrange, " & referencepoint.Address(external:=True, ReferenceStyle:=xlR1C1) & ", mycolumns) End With Cells(d, l).Formula = myresult / (Worksheets("data").R15C2 + (1 / 2)) Next many thanks ! steph -- Dave Peterson -- Dave Peterson |
runtime error 424
What range should myResult point to?
Make sure you include that in your code: set myresult = worksheets("globaal uuroverzicht").range("a1") or something like that. steph wrote: Hi, The messageboxes show that my defined ranges are well set. referencepoint =[test.xls]maaltijdcheques!$L$3 myrange = [test.xls]globaal uuroverzicht!$B$565:$B$577 mycolumns = [test.xls]globaal uuroverzicht!$V$565:$AB$577 but for myresult is still get the runtime errorcode 91 Anymore thoughts of what could be wrong Thanks stephanie "Dave Peterson" schreef: I think I'd add some msgboxes to see what's happening: msgbox myrange.address(external:=true) msgbox referencepoint.address(external:=true) msgbox mycolumns.address(external:=true) msgbox myresult.address(external:=true) I'm guessing that one of these variables isn't set to a real range. steph wrote: Hi Dave The myrange has been defined and used in another part of the macro. When adapting the formula to yours, i receive another errorcode being runtime error 91 (object variable or with block variable not set). I've defined myresult as range and I have omitted the with ... end with. However when I include again the with ... end with, i still get the same errorcode. "Dave Peterson" schreef: You declared myRange and used myResult--but never set them to anything. And when you're assigning a string to that .formular1c1, the you shouldn't use "Set". And I would think that you would want something like: myResult.FormulaR1C1 = "=sumif(" & _ myrange.Address(external:=True, ReferenceStyle:=xlR1C1) _ & "," _ & referencepoint.Address(external:=True, ReferenceStyle:=xlR1C1) _ & "," & mycolumns.Address(external:=True, ReferenceStyle:=xlR1C1) _ & ")" If you're creating the formula, you want to build the string that looks like a formula. If you wanted the code to evaluate that condition, then you'd use application.worksheetfunction.sumif(). steph wrote: Hi, I'm having problems defining my sumif-formula. It returns a runtime error 424 and I don't know how to solve this problem. The formula is searching in a dynamic range and has to sum the values in a number of specific columns when the condition is met. Enclosed you will find the part of the macro which is returning the error. Sub cmdok_click() Dim rij As Integer Dim myrange As Range Dim myselection As Range Dim mycolumns As Range Dim referencepoint As Range For l = 12 To 23 Set referencepoint = Sheets("maaltijdcheques").Cells(3, l) With Worksheets("globaal uuroverzicht") Set mycolumns = .Range("V:AB") End With With Worksheets("globaal uuroverzicht") Set myresult.FormulaR1C1 = Application.WorksheetFunction.SumIf(myrange, " & referencepoint.Address(external:=True, ReferenceStyle:=xlR1C1) & ", mycolumns) End With Cells(d, l).Formula = myresult / (Worksheets("data").R15C2 + (1 / 2)) Next many thanks ! steph -- Dave Peterson -- Dave Peterson -- Dave Peterson |
runtime error 424
I don't understand why this have to be included. The myresult is only a
variable which will be used later to define a cell. It does not need to be referenced to a cell (unless this is why it is not working?) So far I've made following adaptions : Sub cmdok_click() Dim rij As Integer Dim myrange As Range Dim myselection As Range Dim mycolumns As Range Dim referencepoint As Range Dim myresult As Range For z = 11 To 31 myfirstcell = Sheets("globaal uuroverzicht").Cells(W, z).Address mylastcell = Sheets("globaal uuroverzicht").Cells(y, z).Address Sheets("globaal uuroverzicht").Cells(f + 13, z).Formula = "=sum(" & myfirstcell & " :" & mylastcell & " )" Sheets("globaal uuroverzicht").Cells(f + 13, z).Font.Bold = True myfirstcell = Sheets("globaal uuroverzicht").Cells(f, 37).Address mylastcell = Sheets("globaal uuroverzicht").Cells(f + 13, 19).Address Sheets("globaal uuroverzicht").Cells(f + 13, 37).Formula = "=(" & myfirstcell & " - " & mylastcell & ")" Sheets("globaal uuroverzicht").Cells(f + 13, 37).Font.Bold = True Sheets("globaal uuroverzicht").Cells(f + 13, 39) = Sheets("globaal uuroverzicht").Cells(f, 38) + Sheets("globaal uuroverzicht").Cells(f + 13, 26) + Sheets("globaal uuroverzicht").Cells(f + 13, 27) + Sheets("globaal uuroverzicht").Cells(f + 13, 28) + Sheets("globaal uuroverzicht").Cells(f + 13, 29) + Sheets("globaal uuroverzicht").Cells(f + 13, 30) - Sheets("globaal uuroverzicht").Cells(f + 13, 31) Sheets("globaal uuroverzicht").Cells(f + 13, 39).Font.Bold = True mycell = Sheets("globaal uuroverzicht").Cells(f + 13, 37).Address i = Sheets("globaal uuroverzicht").Cells(f, z).Row j = Sheets("globaal uuroverzicht").Cells(f + 12, 37).Row With Worksheets("globaal uuroverzicht") Set myrange = .Range(Cells(i, 2), Cells(j, 2)) Set mycolumns = .Range(Cells(i, 22), Cells(j, 28)) End With Rows(i - 2).Select Selection.Copy Range(Rows(i), Rows(j)).Select Selection.PasteSpecial Paste:=xlPasteFormats, operation:=xlNone, skipblanks:=False, Transpose:=False Application.CutCopyMode = False Next Cells(6, 1).Select End If Next Sheets("maaltijdcheques").Select Sheets("maaltijdcheques").Cells(4, 1).Select Selection.End(xlDown).Select ActiveCell.Offset(1, 0).Range("A1").Select For d = 4 To 60 If Sheets("maaltijdcheques").Cells(d, 1) = ActiveCell Then Sheets("maaltijdcheques").Cells(d, 1).Value = txtSDnr.Text Sheets("maaltijdcheques").Cells(d, 2).Value = txtvoornaam.Text Sheets("maaltijdcheques").Cells(d, 3) = txtachternaam.Text Sheets("maaltijdcheques").Cells(d, 4) = txtgeboortedatum.Text Sheets("maaltijdcheques").Cells(d, 5) = txtindienst.Text Sheets("maaltijdcheques").Cells(d, 7) = txtABM.Text Sheets("maaltijdcheques").Cells(d, 8) = txtMF.Text Sheets("maaltijdcheques").Cells(d, 9) = cboafdeling.Text Sheets("maaltijdcheques").Cells(d, 10) = cbowerkregime.Text For l = 12 To 23 Set referencepoint = Sheets("maaltijdcheques").Cells(3, l) MsgBox referencepoint.Address(external:=True) MsgBox myrange.Address(external:=True) MsgBox mycolumns.Address(external:=True) With Worksheets("globaal uuroverzicht") Set myresult.FormulaR1C1 = "=SumIf(" & myrange.Address(external:=True, ReferenceStyle:=xlR1C1) & ", " & referencepoint.Address(external:=True, ReferenceStyle:=xlR1C1) & "," & mycolumns.Address(external:=True, ReferenceStyle:=xlR1C1) & ")" MsgBox myresult.Address(external:=True) End With Cells(d, l).Formula = myresult / Worksheets("data").R15C2 + (1 / 2) Next End If Next This is still not the complete macro, but I suppose this part will give you more information on what I am doing possibly wrong. The problem is that I have on different sheets variable ranges which need to be linked to each other using formulas. Hope this will give you more information to find a possible solution. Steph "Dave Peterson" schreef: What range should myResult point to? Make sure you include that in your code: set myresult = worksheets("globaal uuroverzicht").range("a1") or something like that. steph wrote: Hi, The messageboxes show that my defined ranges are well set. referencepoint =[test.xls]maaltijdcheques!$L$3 myrange = [test.xls]globaal uuroverzicht!$B$565:$B$577 mycolumns = [test.xls]globaal uuroverzicht!$V$565:$AB$577 but for myresult is still get the runtime errorcode 91 Anymore thoughts of what could be wrong Thanks stephanie "Dave Peterson" schreef: I think I'd add some msgboxes to see what's happening: msgbox myrange.address(external:=true) msgbox referencepoint.address(external:=true) msgbox mycolumns.address(external:=true) msgbox myresult.address(external:=true) I'm guessing that one of these variables isn't set to a real range. steph wrote: Hi Dave The myrange has been defined and used in another part of the macro. When adapting the formula to yours, i receive another errorcode being runtime error 91 (object variable or with block variable not set). I've defined myresult as range and I have omitted the with ... end with. However when I include again the with ... end with, i still get the same errorcode. "Dave Peterson" schreef: You declared myRange and used myResult--but never set them to anything. And when you're assigning a string to that .formular1c1, the you shouldn't use "Set". And I would think that you would want something like: myResult.FormulaR1C1 = "=sumif(" & _ myrange.Address(external:=True, ReferenceStyle:=xlR1C1) _ & "," _ & referencepoint.Address(external:=True, ReferenceStyle:=xlR1C1) _ & "," & mycolumns.Address(external:=True, ReferenceStyle:=xlR1C1) _ & ")" If you're creating the formula, you want to build the string that looks like a formula. If you wanted the code to evaluate that condition, then you'd use application.worksheetfunction.sumif(). steph wrote: Hi, I'm having problems defining my sumif-formula. It returns a runtime error 424 and I don't know how to solve this problem. The formula is searching in a dynamic range and has to sum the values in a number of specific columns when the condition is met. Enclosed you will find the part of the macro which is returning the error. Sub cmdok_click() Dim rij As Integer Dim myrange As Range Dim myselection As Range Dim mycolumns As Range Dim referencepoint As Range For l = 12 To 23 Set referencepoint = Sheets("maaltijdcheques").Cells(3, l) With Worksheets("globaal uuroverzicht") Set mycolumns = .Range("V:AB") End With With Worksheets("globaal uuroverzicht") Set myresult.FormulaR1C1 = Application.WorksheetFunction.SumIf(myrange, " & referencepoint.Address(external:=True, ReferenceStyle:=xlR1C1) & ", mycolumns) End With Cells(d, l).Formula = myresult / (Worksheets("data").R15C2 + (1 / 2)) Next many thanks ! steph -- Dave Peterson -- Dave Peterson -- Dave Peterson |
runtime error 424
When you use myResult.formular1c1, then myresult has to be a range--not just a
range object (that might not be set). If you want to just hold that string, then declare a string and use that: dim myFormulaR1C1Str as string myformulaR1C1Str = "....." steph wrote: I don't understand why this have to be included. The myresult is only a variable which will be used later to define a cell. It does not need to be referenced to a cell (unless this is why it is not working?) So far I've made following adaptions : Sub cmdok_click() Dim rij As Integer Dim myrange As Range Dim myselection As Range Dim mycolumns As Range Dim referencepoint As Range Dim myresult As Range For z = 11 To 31 myfirstcell = Sheets("globaal uuroverzicht").Cells(W, z).Address mylastcell = Sheets("globaal uuroverzicht").Cells(y, z).Address Sheets("globaal uuroverzicht").Cells(f + 13, z).Formula = "=sum(" & myfirstcell & " :" & mylastcell & " )" Sheets("globaal uuroverzicht").Cells(f + 13, z).Font.Bold = True myfirstcell = Sheets("globaal uuroverzicht").Cells(f, 37).Address mylastcell = Sheets("globaal uuroverzicht").Cells(f + 13, 19).Address Sheets("globaal uuroverzicht").Cells(f + 13, 37).Formula = "=(" & myfirstcell & " - " & mylastcell & ")" Sheets("globaal uuroverzicht").Cells(f + 13, 37).Font.Bold = True Sheets("globaal uuroverzicht").Cells(f + 13, 39) = Sheets("globaal uuroverzicht").Cells(f, 38) + Sheets("globaal uuroverzicht").Cells(f + 13, 26) + Sheets("globaal uuroverzicht").Cells(f + 13, 27) + Sheets("globaal uuroverzicht").Cells(f + 13, 28) + Sheets("globaal uuroverzicht").Cells(f + 13, 29) + Sheets("globaal uuroverzicht").Cells(f + 13, 30) - Sheets("globaal uuroverzicht").Cells(f + 13, 31) Sheets("globaal uuroverzicht").Cells(f + 13, 39).Font.Bold = True mycell = Sheets("globaal uuroverzicht").Cells(f + 13, 37).Address i = Sheets("globaal uuroverzicht").Cells(f, z).Row j = Sheets("globaal uuroverzicht").Cells(f + 12, 37).Row With Worksheets("globaal uuroverzicht") Set myrange = .Range(Cells(i, 2), Cells(j, 2)) Set mycolumns = .Range(Cells(i, 22), Cells(j, 28)) End With Rows(i - 2).Select Selection.Copy Range(Rows(i), Rows(j)).Select Selection.PasteSpecial Paste:=xlPasteFormats, operation:=xlNone, skipblanks:=False, Transpose:=False Application.CutCopyMode = False Next Cells(6, 1).Select End If Next Sheets("maaltijdcheques").Select Sheets("maaltijdcheques").Cells(4, 1).Select Selection.End(xlDown).Select ActiveCell.Offset(1, 0).Range("A1").Select For d = 4 To 60 If Sheets("maaltijdcheques").Cells(d, 1) = ActiveCell Then Sheets("maaltijdcheques").Cells(d, 1).Value = txtSDnr.Text Sheets("maaltijdcheques").Cells(d, 2).Value = txtvoornaam.Text Sheets("maaltijdcheques").Cells(d, 3) = txtachternaam.Text Sheets("maaltijdcheques").Cells(d, 4) = txtgeboortedatum.Text Sheets("maaltijdcheques").Cells(d, 5) = txtindienst.Text Sheets("maaltijdcheques").Cells(d, 7) = txtABM.Text Sheets("maaltijdcheques").Cells(d, 8) = txtMF.Text Sheets("maaltijdcheques").Cells(d, 9) = cboafdeling.Text Sheets("maaltijdcheques").Cells(d, 10) = cbowerkregime.Text For l = 12 To 23 Set referencepoint = Sheets("maaltijdcheques").Cells(3, l) MsgBox referencepoint.Address(external:=True) MsgBox myrange.Address(external:=True) MsgBox mycolumns.Address(external:=True) With Worksheets("globaal uuroverzicht") Set myresult.FormulaR1C1 = "=SumIf(" & myrange.Address(external:=True, ReferenceStyle:=xlR1C1) & ", " & referencepoint.Address(external:=True, ReferenceStyle:=xlR1C1) & "," & mycolumns.Address(external:=True, ReferenceStyle:=xlR1C1) & ")" MsgBox myresult.Address(external:=True) End With Cells(d, l).Formula = myresult / Worksheets("data").R15C2 + (1 / 2) Next End If Next This is still not the complete macro, but I suppose this part will give you more information on what I am doing possibly wrong. The problem is that I have on different sheets variable ranges which need to be linked to each other using formulas. Hope this will give you more information to find a possible solution. Steph "Dave Peterson" schreef: What range should myResult point to? Make sure you include that in your code: set myresult = worksheets("globaal uuroverzicht").range("a1") or something like that. steph wrote: Hi, The messageboxes show that my defined ranges are well set. referencepoint =[test.xls]maaltijdcheques!$L$3 myrange = [test.xls]globaal uuroverzicht!$B$565:$B$577 mycolumns = [test.xls]globaal uuroverzicht!$V$565:$AB$577 but for myresult is still get the runtime errorcode 91 Anymore thoughts of what could be wrong Thanks stephanie "Dave Peterson" schreef: I think I'd add some msgboxes to see what's happening: msgbox myrange.address(external:=true) msgbox referencepoint.address(external:=true) msgbox mycolumns.address(external:=true) msgbox myresult.address(external:=true) I'm guessing that one of these variables isn't set to a real range. steph wrote: Hi Dave The myrange has been defined and used in another part of the macro. When adapting the formula to yours, i receive another errorcode being runtime error 91 (object variable or with block variable not set). I've defined myresult as range and I have omitted the with ... end with. However when I include again the with ... end with, i still get the same errorcode. "Dave Peterson" schreef: You declared myRange and used myResult--but never set them to anything. And when you're assigning a string to that .formular1c1, the you shouldn't use "Set". And I would think that you would want something like: myResult.FormulaR1C1 = "=sumif(" & _ myrange.Address(external:=True, ReferenceStyle:=xlR1C1) _ & "," _ & referencepoint.Address(external:=True, ReferenceStyle:=xlR1C1) _ & "," & mycolumns.Address(external:=True, ReferenceStyle:=xlR1C1) _ & ")" If you're creating the formula, you want to build the string that looks like a formula. If you wanted the code to evaluate that condition, then you'd use application.worksheetfunction.sumif(). steph wrote: Hi, I'm having problems defining my sumif-formula. It returns a runtime error 424 and I don't know how to solve this problem. The formula is searching in a dynamic range and has to sum the values in a number of specific columns when the condition is met. Enclosed you will find the part of the macro which is returning the error. Sub cmdok_click() Dim rij As Integer Dim myrange As Range Dim myselection As Range Dim mycolumns As Range Dim referencepoint As Range For l = 12 To 23 Set referencepoint = Sheets("maaltijdcheques").Cells(3, l) With Worksheets("globaal uuroverzicht") Set mycolumns = .Range("V:AB") End With With Worksheets("globaal uuroverzicht") Set myresult.FormulaR1C1 = Application.WorksheetFunction.SumIf(myrange, " & referencepoint.Address(external:=True, ReferenceStyle:=xlR1C1) & ", mycolumns) End With Cells(d, l).Formula = myresult / (Worksheets("data").R15C2 + (1 / 2)) Next many thanks ! steph -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
runtime error 424
Hi,
still no luck ! The next problem I'm facing is that the string has to be linked to a cell. The myresultStr has been defined but now I need to have this result in cell (d,l). How is this possible ? Steph "Dave Peterson" schreef: When you use myResult.formular1c1, then myresult has to be a range--not just a range object (that might not be set). If you want to just hold that string, then declare a string and use that: dim myFormulaR1C1Str as string myformulaR1C1Str = "....." steph wrote: I don't understand why this have to be included. The myresult is only a variable which will be used later to define a cell. It does not need to be referenced to a cell (unless this is why it is not working?) So far I've made following adaptions : Sub cmdok_click() Dim rij As Integer Dim myrange As Range Dim myselection As Range Dim mycolumns As Range Dim referencepoint As Range Dim myresult As Range For z = 11 To 31 myfirstcell = Sheets("globaal uuroverzicht").Cells(W, z).Address mylastcell = Sheets("globaal uuroverzicht").Cells(y, z).Address Sheets("globaal uuroverzicht").Cells(f + 13, z).Formula = "=sum(" & myfirstcell & " :" & mylastcell & " )" Sheets("globaal uuroverzicht").Cells(f + 13, z).Font.Bold = True myfirstcell = Sheets("globaal uuroverzicht").Cells(f, 37).Address mylastcell = Sheets("globaal uuroverzicht").Cells(f + 13, 19).Address Sheets("globaal uuroverzicht").Cells(f + 13, 37).Formula = "=(" & myfirstcell & " - " & mylastcell & ")" Sheets("globaal uuroverzicht").Cells(f + 13, 37).Font.Bold = True Sheets("globaal uuroverzicht").Cells(f + 13, 39) = Sheets("globaal uuroverzicht").Cells(f, 38) + Sheets("globaal uuroverzicht").Cells(f + 13, 26) + Sheets("globaal uuroverzicht").Cells(f + 13, 27) + Sheets("globaal uuroverzicht").Cells(f + 13, 28) + Sheets("globaal uuroverzicht").Cells(f + 13, 29) + Sheets("globaal uuroverzicht").Cells(f + 13, 30) - Sheets("globaal uuroverzicht").Cells(f + 13, 31) Sheets("globaal uuroverzicht").Cells(f + 13, 39).Font.Bold = True mycell = Sheets("globaal uuroverzicht").Cells(f + 13, 37).Address i = Sheets("globaal uuroverzicht").Cells(f, z).Row j = Sheets("globaal uuroverzicht").Cells(f + 12, 37).Row With Worksheets("globaal uuroverzicht") Set myrange = .Range(Cells(i, 2), Cells(j, 2)) Set mycolumns = .Range(Cells(i, 22), Cells(j, 28)) End With Rows(i - 2).Select Selection.Copy Range(Rows(i), Rows(j)).Select Selection.PasteSpecial Paste:=xlPasteFormats, operation:=xlNone, skipblanks:=False, Transpose:=False Application.CutCopyMode = False Next Cells(6, 1).Select End If Next Sheets("maaltijdcheques").Select Sheets("maaltijdcheques").Cells(4, 1).Select Selection.End(xlDown).Select ActiveCell.Offset(1, 0).Range("A1").Select For d = 4 To 60 If Sheets("maaltijdcheques").Cells(d, 1) = ActiveCell Then Sheets("maaltijdcheques").Cells(d, 1).Value = txtSDnr.Text Sheets("maaltijdcheques").Cells(d, 2).Value = txtvoornaam.Text Sheets("maaltijdcheques").Cells(d, 3) = txtachternaam.Text Sheets("maaltijdcheques").Cells(d, 4) = txtgeboortedatum.Text Sheets("maaltijdcheques").Cells(d, 5) = txtindienst.Text Sheets("maaltijdcheques").Cells(d, 7) = txtABM.Text Sheets("maaltijdcheques").Cells(d, 8) = txtMF.Text Sheets("maaltijdcheques").Cells(d, 9) = cboafdeling.Text Sheets("maaltijdcheques").Cells(d, 10) = cbowerkregime.Text For l = 12 To 23 Set referencepoint = Sheets("maaltijdcheques").Cells(3, l) MsgBox referencepoint.Address(external:=True) MsgBox myrange.Address(external:=True) MsgBox mycolumns.Address(external:=True) With Worksheets("globaal uuroverzicht") Set myresult.FormulaR1C1 = "=SumIf(" & myrange.Address(external:=True, ReferenceStyle:=xlR1C1) & ", " & referencepoint.Address(external:=True, ReferenceStyle:=xlR1C1) & "," & mycolumns.Address(external:=True, ReferenceStyle:=xlR1C1) & ")" MsgBox myresult.Address(external:=True) End With Cells(d, l).Formula = myresult / Worksheets("data").R15C2 + (1 / 2) Next End If Next This is still not the complete macro, but I suppose this part will give you more information on what I am doing possibly wrong. The problem is that I have on different sheets variable ranges which need to be linked to each other using formulas. Hope this will give you more information to find a possible solution. Steph "Dave Peterson" schreef: What range should myResult point to? Make sure you include that in your code: set myresult = worksheets("globaal uuroverzicht").range("a1") or something like that. steph wrote: Hi, The messageboxes show that my defined ranges are well set. referencepoint =[test.xls]maaltijdcheques!$L$3 myrange = [test.xls]globaal uuroverzicht!$B$565:$B$577 mycolumns = [test.xls]globaal uuroverzicht!$V$565:$AB$577 but for myresult is still get the runtime errorcode 91 Anymore thoughts of what could be wrong Thanks stephanie "Dave Peterson" schreef: I think I'd add some msgboxes to see what's happening: msgbox myrange.address(external:=true) msgbox referencepoint.address(external:=true) msgbox mycolumns.address(external:=true) msgbox myresult.address(external:=true) I'm guessing that one of these variables isn't set to a real range. steph wrote: Hi Dave The myrange has been defined and used in another part of the macro. When adapting the formula to yours, i receive another errorcode being runtime error 91 (object variable or with block variable not set). I've defined myresult as range and I have omitted the with ... end with. However when I include again the with ... end with, i still get the same errorcode. "Dave Peterson" schreef: You declared myRange and used myResult--but never set them to anything. And when you're assigning a string to that .formular1c1, the you shouldn't use "Set". And I would think that you would want something like: myResult.FormulaR1C1 = "=sumif(" & _ myrange.Address(external:=True, ReferenceStyle:=xlR1C1) _ & "," _ & referencepoint.Address(external:=True, ReferenceStyle:=xlR1C1) _ & "," & mycolumns.Address(external:=True, ReferenceStyle:=xlR1C1) _ & ")" If you're creating the formula, you want to build the string that looks like a formula. If you wanted the code to evaluate that condition, then you'd use application.worksheetfunction.sumif(). steph wrote: Hi, I'm having problems defining my sumif-formula. It returns a runtime error 424 and I don't know how to solve this problem. The formula is searching in a dynamic range and has to sum the values in a number of specific columns when the condition is met. Enclosed you will find the part of the macro which is returning the error. Sub cmdok_click() Dim rij As Integer Dim myrange As Range Dim myselection As Range Dim mycolumns As Range Dim referencepoint As Range For l = 12 To 23 Set referencepoint = Sheets("maaltijdcheques").Cells(3, l) With Worksheets("globaal uuroverzicht") Set mycolumns = .Range("V:AB") End With With Worksheets("globaal uuroverzicht") Set myresult.FormulaR1C1 = Application.WorksheetFunction.SumIf(myrange, " & referencepoint.Address(external:=True, ReferenceStyle:=xlR1C1) & ", mycolumns) End With Cells(d, l).Formula = myresult / (Worksheets("data").R15C2 + (1 / 2)) Next many thanks ! steph -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
runtime error 424
worksheets("some sheet name here").cells(1,"D").formular1c1 _
= myresultStr Maybe???? steph wrote: Hi, still no luck ! The next problem I'm facing is that the string has to be linked to a cell. The myresultStr has been defined but now I need to have this result in cell (d,l). How is this possible ? Steph "Dave Peterson" schreef: When you use myResult.formular1c1, then myresult has to be a range--not just a range object (that might not be set). If you want to just hold that string, then declare a string and use that: dim myFormulaR1C1Str as string myformulaR1C1Str = "....." steph wrote: I don't understand why this have to be included. The myresult is only a variable which will be used later to define a cell. It does not need to be referenced to a cell (unless this is why it is not working?) So far I've made following adaptions : Sub cmdok_click() Dim rij As Integer Dim myrange As Range Dim myselection As Range Dim mycolumns As Range Dim referencepoint As Range Dim myresult As Range For z = 11 To 31 myfirstcell = Sheets("globaal uuroverzicht").Cells(W, z).Address mylastcell = Sheets("globaal uuroverzicht").Cells(y, z).Address Sheets("globaal uuroverzicht").Cells(f + 13, z).Formula = "=sum(" & myfirstcell & " :" & mylastcell & " )" Sheets("globaal uuroverzicht").Cells(f + 13, z).Font.Bold = True myfirstcell = Sheets("globaal uuroverzicht").Cells(f, 37).Address mylastcell = Sheets("globaal uuroverzicht").Cells(f + 13, 19).Address Sheets("globaal uuroverzicht").Cells(f + 13, 37).Formula = "=(" & myfirstcell & " - " & mylastcell & ")" Sheets("globaal uuroverzicht").Cells(f + 13, 37).Font.Bold = True Sheets("globaal uuroverzicht").Cells(f + 13, 39) = Sheets("globaal uuroverzicht").Cells(f, 38) + Sheets("globaal uuroverzicht").Cells(f + 13, 26) + Sheets("globaal uuroverzicht").Cells(f + 13, 27) + Sheets("globaal uuroverzicht").Cells(f + 13, 28) + Sheets("globaal uuroverzicht").Cells(f + 13, 29) + Sheets("globaal uuroverzicht").Cells(f + 13, 30) - Sheets("globaal uuroverzicht").Cells(f + 13, 31) Sheets("globaal uuroverzicht").Cells(f + 13, 39).Font.Bold = True mycell = Sheets("globaal uuroverzicht").Cells(f + 13, 37).Address i = Sheets("globaal uuroverzicht").Cells(f, z).Row j = Sheets("globaal uuroverzicht").Cells(f + 12, 37).Row With Worksheets("globaal uuroverzicht") Set myrange = .Range(Cells(i, 2), Cells(j, 2)) Set mycolumns = .Range(Cells(i, 22), Cells(j, 28)) End With Rows(i - 2).Select Selection.Copy Range(Rows(i), Rows(j)).Select Selection.PasteSpecial Paste:=xlPasteFormats, operation:=xlNone, skipblanks:=False, Transpose:=False Application.CutCopyMode = False Next Cells(6, 1).Select End If Next Sheets("maaltijdcheques").Select Sheets("maaltijdcheques").Cells(4, 1).Select Selection.End(xlDown).Select ActiveCell.Offset(1, 0).Range("A1").Select For d = 4 To 60 If Sheets("maaltijdcheques").Cells(d, 1) = ActiveCell Then Sheets("maaltijdcheques").Cells(d, 1).Value = txtSDnr.Text Sheets("maaltijdcheques").Cells(d, 2).Value = txtvoornaam.Text Sheets("maaltijdcheques").Cells(d, 3) = txtachternaam.Text Sheets("maaltijdcheques").Cells(d, 4) = txtgeboortedatum.Text Sheets("maaltijdcheques").Cells(d, 5) = txtindienst.Text Sheets("maaltijdcheques").Cells(d, 7) = txtABM.Text Sheets("maaltijdcheques").Cells(d, 8) = txtMF.Text Sheets("maaltijdcheques").Cells(d, 9) = cboafdeling.Text Sheets("maaltijdcheques").Cells(d, 10) = cbowerkregime.Text For l = 12 To 23 Set referencepoint = Sheets("maaltijdcheques").Cells(3, l) MsgBox referencepoint.Address(external:=True) MsgBox myrange.Address(external:=True) MsgBox mycolumns.Address(external:=True) With Worksheets("globaal uuroverzicht") Set myresult.FormulaR1C1 = "=SumIf(" & myrange.Address(external:=True, ReferenceStyle:=xlR1C1) & ", " & referencepoint.Address(external:=True, ReferenceStyle:=xlR1C1) & "," & mycolumns.Address(external:=True, ReferenceStyle:=xlR1C1) & ")" MsgBox myresult.Address(external:=True) End With Cells(d, l).Formula = myresult / Worksheets("data").R15C2 + (1 / 2) Next End If Next This is still not the complete macro, but I suppose this part will give you more information on what I am doing possibly wrong. The problem is that I have on different sheets variable ranges which need to be linked to each other using formulas. Hope this will give you more information to find a possible solution. Steph "Dave Peterson" schreef: What range should myResult point to? Make sure you include that in your code: set myresult = worksheets("globaal uuroverzicht").range("a1") or something like that. steph wrote: Hi, The messageboxes show that my defined ranges are well set. referencepoint =[test.xls]maaltijdcheques!$L$3 myrange = [test.xls]globaal uuroverzicht!$B$565:$B$577 mycolumns = [test.xls]globaal uuroverzicht!$V$565:$AB$577 but for myresult is still get the runtime errorcode 91 Anymore thoughts of what could be wrong Thanks stephanie "Dave Peterson" schreef: I think I'd add some msgboxes to see what's happening: msgbox myrange.address(external:=true) msgbox referencepoint.address(external:=true) msgbox mycolumns.address(external:=true) msgbox myresult.address(external:=true) I'm guessing that one of these variables isn't set to a real range. steph wrote: Hi Dave The myrange has been defined and used in another part of the macro. When adapting the formula to yours, i receive another errorcode being runtime error 91 (object variable or with block variable not set). I've defined myresult as range and I have omitted the with ... end with. However when I include again the with ... end with, i still get the same errorcode. "Dave Peterson" schreef: You declared myRange and used myResult--but never set them to anything. And when you're assigning a string to that .formular1c1, the you shouldn't use "Set". And I would think that you would want something like: myResult.FormulaR1C1 = "=sumif(" & _ myrange.Address(external:=True, ReferenceStyle:=xlR1C1) _ & "," _ & referencepoint.Address(external:=True, ReferenceStyle:=xlR1C1) _ & "," & mycolumns.Address(external:=True, ReferenceStyle:=xlR1C1) _ & ")" If you're creating the formula, you want to build the string that looks like a formula. If you wanted the code to evaluate that condition, then you'd use application.worksheetfunction.sumif(). steph wrote: Hi, I'm having problems defining my sumif-formula. It returns a runtime error 424 and I don't know how to solve this problem. The formula is searching in a dynamic range and has to sum the values in a number of specific columns when the condition is met. Enclosed you will find the part of the macro which is returning the error. Sub cmdok_click() Dim rij As Integer Dim myrange As Range Dim myselection As Range Dim mycolumns As Range Dim referencepoint As Range For l = 12 To 23 Set referencepoint = Sheets("maaltijdcheques").Cells(3, l) With Worksheets("globaal uuroverzicht") Set mycolumns = .Range("V:AB") End With With Worksheets("globaal uuroverzicht") Set myresult.FormulaR1C1 = Application.WorksheetFunction.SumIf(myrange, " & referencepoint.Address(external:=True, ReferenceStyle:=xlR1C1) & ", mycolumns) End With Cells(d, l).Formula = myresult / (Worksheets("data").R15C2 + (1 / 2)) Next many thanks ! steph -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
runtime error 424
Yes, that is what I also thought but it seems not to be working. I receive
an errorcode 1004, allthough the myresultStr is defined. Can it not be something because myresultStr is a string and I need to attach the formula to a cell ? Any ideas ? I'm so close finalizing the macro but this part seems to bother me a lot. Thanks stephanie "Dave Peterson" schreef: worksheets("some sheet name here").cells(1,"D").formular1c1 _ = myresultStr Maybe???? steph wrote: Hi, still no luck ! The next problem I'm facing is that the string has to be linked to a cell. The myresultStr has been defined but now I need to have this result in cell (d,l). How is this possible ? Steph "Dave Peterson" schreef: When you use myResult.formular1c1, then myresult has to be a range--not just a range object (that might not be set). If you want to just hold that string, then declare a string and use that: dim myFormulaR1C1Str as string myformulaR1C1Str = "....." steph wrote: I don't understand why this have to be included. The myresult is only a variable which will be used later to define a cell. It does not need to be referenced to a cell (unless this is why it is not working?) So far I've made following adaptions : Sub cmdok_click() Dim rij As Integer Dim myrange As Range Dim myselection As Range Dim mycolumns As Range Dim referencepoint As Range Dim myresult As Range For z = 11 To 31 myfirstcell = Sheets("globaal uuroverzicht").Cells(W, z).Address mylastcell = Sheets("globaal uuroverzicht").Cells(y, z).Address Sheets("globaal uuroverzicht").Cells(f + 13, z).Formula = "=sum(" & myfirstcell & " :" & mylastcell & " )" Sheets("globaal uuroverzicht").Cells(f + 13, z).Font.Bold = True myfirstcell = Sheets("globaal uuroverzicht").Cells(f, 37).Address mylastcell = Sheets("globaal uuroverzicht").Cells(f + 13, 19).Address Sheets("globaal uuroverzicht").Cells(f + 13, 37).Formula = "=(" & myfirstcell & " - " & mylastcell & ")" Sheets("globaal uuroverzicht").Cells(f + 13, 37).Font.Bold = True Sheets("globaal uuroverzicht").Cells(f + 13, 39) = Sheets("globaal uuroverzicht").Cells(f, 38) + Sheets("globaal uuroverzicht").Cells(f + 13, 26) + Sheets("globaal uuroverzicht").Cells(f + 13, 27) + Sheets("globaal uuroverzicht").Cells(f + 13, 28) + Sheets("globaal uuroverzicht").Cells(f + 13, 29) + Sheets("globaal uuroverzicht").Cells(f + 13, 30) - Sheets("globaal uuroverzicht").Cells(f + 13, 31) Sheets("globaal uuroverzicht").Cells(f + 13, 39).Font.Bold = True mycell = Sheets("globaal uuroverzicht").Cells(f + 13, 37).Address i = Sheets("globaal uuroverzicht").Cells(f, z).Row j = Sheets("globaal uuroverzicht").Cells(f + 12, 37).Row With Worksheets("globaal uuroverzicht") Set myrange = .Range(Cells(i, 2), Cells(j, 2)) Set mycolumns = .Range(Cells(i, 22), Cells(j, 28)) End With Rows(i - 2).Select Selection.Copy Range(Rows(i), Rows(j)).Select Selection.PasteSpecial Paste:=xlPasteFormats, operation:=xlNone, skipblanks:=False, Transpose:=False Application.CutCopyMode = False Next Cells(6, 1).Select End If Next Sheets("maaltijdcheques").Select Sheets("maaltijdcheques").Cells(4, 1).Select Selection.End(xlDown).Select ActiveCell.Offset(1, 0).Range("A1").Select For d = 4 To 60 If Sheets("maaltijdcheques").Cells(d, 1) = ActiveCell Then Sheets("maaltijdcheques").Cells(d, 1).Value = txtSDnr.Text Sheets("maaltijdcheques").Cells(d, 2).Value = txtvoornaam.Text Sheets("maaltijdcheques").Cells(d, 3) = txtachternaam.Text Sheets("maaltijdcheques").Cells(d, 4) = txtgeboortedatum.Text Sheets("maaltijdcheques").Cells(d, 5) = txtindienst.Text Sheets("maaltijdcheques").Cells(d, 7) = txtABM.Text Sheets("maaltijdcheques").Cells(d, 8) = txtMF.Text Sheets("maaltijdcheques").Cells(d, 9) = cboafdeling.Text Sheets("maaltijdcheques").Cells(d, 10) = cbowerkregime.Text For l = 12 To 23 Set referencepoint = Sheets("maaltijdcheques").Cells(3, l) MsgBox referencepoint.Address(external:=True) MsgBox myrange.Address(external:=True) MsgBox mycolumns.Address(external:=True) With Worksheets("globaal uuroverzicht") Set myresult.FormulaR1C1 = "=SumIf(" & myrange.Address(external:=True, ReferenceStyle:=xlR1C1) & ", " & referencepoint.Address(external:=True, ReferenceStyle:=xlR1C1) & "," & mycolumns.Address(external:=True, ReferenceStyle:=xlR1C1) & ")" MsgBox myresult.Address(external:=True) End With Cells(d, l).Formula = myresult / Worksheets("data").R15C2 + (1 / 2) Next End If Next This is still not the complete macro, but I suppose this part will give you more information on what I am doing possibly wrong. The problem is that I have on different sheets variable ranges which need to be linked to each other using formulas. Hope this will give you more information to find a possible solution. Steph "Dave Peterson" schreef: What range should myResult point to? Make sure you include that in your code: set myresult = worksheets("globaal uuroverzicht").range("a1") or something like that. steph wrote: Hi, The messageboxes show that my defined ranges are well set. referencepoint =[test.xls]maaltijdcheques!$L$3 myrange = [test.xls]globaal uuroverzicht!$B$565:$B$577 mycolumns = [test.xls]globaal uuroverzicht!$V$565:$AB$577 but for myresult is still get the runtime errorcode 91 Anymore thoughts of what could be wrong Thanks stephanie "Dave Peterson" schreef: I think I'd add some msgboxes to see what's happening: msgbox myrange.address(external:=true) msgbox referencepoint.address(external:=true) msgbox mycolumns.address(external:=true) msgbox myresult.address(external:=true) I'm guessing that one of these variables isn't set to a real range. steph wrote: Hi Dave The myrange has been defined and used in another part of the macro. When adapting the formula to yours, i receive another errorcode being runtime error 91 (object variable or with block variable not set). I've defined myresult as range and I have omitted the with ... end with. However when I include again the with ... end with, i still get the same errorcode. "Dave Peterson" schreef: You declared myRange and used myResult--but never set them to anything. And when you're assigning a string to that .formular1c1, the you shouldn't use "Set". And I would think that you would want something like: myResult.FormulaR1C1 = "=sumif(" & _ myrange.Address(external:=True, ReferenceStyle:=xlR1C1) _ & "," _ & referencepoint.Address(external:=True, ReferenceStyle:=xlR1C1) _ & "," & mycolumns.Address(external:=True, ReferenceStyle:=xlR1C1) _ & ")" If you're creating the formula, you want to build the string that looks like a formula. If you wanted the code to evaluate that condition, then you'd use application.worksheetfunction.sumif(). steph wrote: Hi, I'm having problems defining my sumif-formula. It returns a runtime error 424 and I don't know how to solve this problem. The formula is searching in a dynamic range and has to sum the values in a number of specific columns when the condition is met. Enclosed you will find the part of the macro which is returning the error. Sub cmdok_click() Dim rij As Integer Dim myrange As Range Dim myselection As Range Dim mycolumns As Range Dim referencepoint As Range For l = 12 To 23 Set referencepoint = Sheets("maaltijdcheques").Cells(3, l) With Worksheets("globaal uuroverzicht") Set mycolumns = .Range("V:AB") End With With Worksheets("globaal uuroverzicht") Set myresult.FormulaR1C1 = Application.WorksheetFunction.SumIf(myrange, " & referencepoint.Address(external:=True, ReferenceStyle:=xlR1C1) & ", mycolumns) End With Cells(d, l).Formula = myresult / (Worksheets("data").R15C2 + (1 / 2)) Next many thanks ! steph -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
runtime error 424
I think you're going to have to post the snippet of code that isn't working.
steph wrote: Yes, that is what I also thought but it seems not to be working. I receive an errorcode 1004, allthough the myresultStr is defined. Can it not be something because myresultStr is a string and I need to attach the formula to a cell ? Any ideas ? I'm so close finalizing the macro but this part seems to bother me a lot. Thanks stephanie "Dave Peterson" schreef: worksheets("some sheet name here").cells(1,"D").formular1c1 _ = myresultStr Maybe???? steph wrote: Hi, still no luck ! The next problem I'm facing is that the string has to be linked to a cell. The myresultStr has been defined but now I need to have this result in cell (d,l). How is this possible ? Steph "Dave Peterson" schreef: When you use myResult.formular1c1, then myresult has to be a range--not just a range object (that might not be set). If you want to just hold that string, then declare a string and use that: dim myFormulaR1C1Str as string myformulaR1C1Str = "....." steph wrote: I don't understand why this have to be included. The myresult is only a variable which will be used later to define a cell. It does not need to be referenced to a cell (unless this is why it is not working?) So far I've made following adaptions : Sub cmdok_click() Dim rij As Integer Dim myrange As Range Dim myselection As Range Dim mycolumns As Range Dim referencepoint As Range Dim myresult As Range For z = 11 To 31 myfirstcell = Sheets("globaal uuroverzicht").Cells(W, z).Address mylastcell = Sheets("globaal uuroverzicht").Cells(y, z).Address Sheets("globaal uuroverzicht").Cells(f + 13, z).Formula = "=sum(" & myfirstcell & " :" & mylastcell & " )" Sheets("globaal uuroverzicht").Cells(f + 13, z).Font.Bold = True myfirstcell = Sheets("globaal uuroverzicht").Cells(f, 37).Address mylastcell = Sheets("globaal uuroverzicht").Cells(f + 13, 19).Address Sheets("globaal uuroverzicht").Cells(f + 13, 37).Formula = "=(" & myfirstcell & " - " & mylastcell & ")" Sheets("globaal uuroverzicht").Cells(f + 13, 37).Font.Bold = True Sheets("globaal uuroverzicht").Cells(f + 13, 39) = Sheets("globaal uuroverzicht").Cells(f, 38) + Sheets("globaal uuroverzicht").Cells(f + 13, 26) + Sheets("globaal uuroverzicht").Cells(f + 13, 27) + Sheets("globaal uuroverzicht").Cells(f + 13, 28) + Sheets("globaal uuroverzicht").Cells(f + 13, 29) + Sheets("globaal uuroverzicht").Cells(f + 13, 30) - Sheets("globaal uuroverzicht").Cells(f + 13, 31) Sheets("globaal uuroverzicht").Cells(f + 13, 39).Font.Bold = True mycell = Sheets("globaal uuroverzicht").Cells(f + 13, 37).Address i = Sheets("globaal uuroverzicht").Cells(f, z).Row j = Sheets("globaal uuroverzicht").Cells(f + 12, 37).Row With Worksheets("globaal uuroverzicht") Set myrange = .Range(Cells(i, 2), Cells(j, 2)) Set mycolumns = .Range(Cells(i, 22), Cells(j, 28)) End With Rows(i - 2).Select Selection.Copy Range(Rows(i), Rows(j)).Select Selection.PasteSpecial Paste:=xlPasteFormats, operation:=xlNone, skipblanks:=False, Transpose:=False Application.CutCopyMode = False Next Cells(6, 1).Select End If Next Sheets("maaltijdcheques").Select Sheets("maaltijdcheques").Cells(4, 1).Select Selection.End(xlDown).Select ActiveCell.Offset(1, 0).Range("A1").Select For d = 4 To 60 If Sheets("maaltijdcheques").Cells(d, 1) = ActiveCell Then Sheets("maaltijdcheques").Cells(d, 1).Value = txtSDnr.Text Sheets("maaltijdcheques").Cells(d, 2).Value = txtvoornaam.Text Sheets("maaltijdcheques").Cells(d, 3) = txtachternaam.Text Sheets("maaltijdcheques").Cells(d, 4) = txtgeboortedatum.Text Sheets("maaltijdcheques").Cells(d, 5) = txtindienst.Text Sheets("maaltijdcheques").Cells(d, 7) = txtABM.Text Sheets("maaltijdcheques").Cells(d, 8) = txtMF.Text Sheets("maaltijdcheques").Cells(d, 9) = cboafdeling.Text Sheets("maaltijdcheques").Cells(d, 10) = cbowerkregime.Text For l = 12 To 23 Set referencepoint = Sheets("maaltijdcheques").Cells(3, l) MsgBox referencepoint.Address(external:=True) MsgBox myrange.Address(external:=True) MsgBox mycolumns.Address(external:=True) With Worksheets("globaal uuroverzicht") Set myresult.FormulaR1C1 = "=SumIf(" & myrange.Address(external:=True, ReferenceStyle:=xlR1C1) & ", " & referencepoint.Address(external:=True, ReferenceStyle:=xlR1C1) & "," & mycolumns.Address(external:=True, ReferenceStyle:=xlR1C1) & ")" MsgBox myresult.Address(external:=True) End With Cells(d, l).Formula = myresult / Worksheets("data").R15C2 + (1 / 2) Next End If Next This is still not the complete macro, but I suppose this part will give you more information on what I am doing possibly wrong. The problem is that I have on different sheets variable ranges which need to be linked to each other using formulas. Hope this will give you more information to find a possible solution. Steph "Dave Peterson" schreef: What range should myResult point to? Make sure you include that in your code: set myresult = worksheets("globaal uuroverzicht").range("a1") or something like that. steph wrote: Hi, The messageboxes show that my defined ranges are well set. referencepoint =[test.xls]maaltijdcheques!$L$3 myrange = [test.xls]globaal uuroverzicht!$B$565:$B$577 mycolumns = [test.xls]globaal uuroverzicht!$V$565:$AB$577 but for myresult is still get the runtime errorcode 91 Anymore thoughts of what could be wrong Thanks stephanie "Dave Peterson" schreef: I think I'd add some msgboxes to see what's happening: msgbox myrange.address(external:=true) msgbox referencepoint.address(external:=true) msgbox mycolumns.address(external:=true) msgbox myresult.address(external:=true) I'm guessing that one of these variables isn't set to a real range. steph wrote: Hi Dave The myrange has been defined and used in another part of the macro. When adapting the formula to yours, i receive another errorcode being runtime error 91 (object variable or with block variable not set). I've defined myresult as range and I have omitted the with ... end with. However when I include again the with ... end with, i still get the same errorcode. "Dave Peterson" schreef: You declared myRange and used myResult--but never set them to anything. And when you're assigning a string to that .formular1c1, the you shouldn't use "Set". And I would think that you would want something like: myResult.FormulaR1C1 = "=sumif(" & _ myrange.Address(external:=True, ReferenceStyle:=xlR1C1) _ & "," _ & referencepoint.Address(external:=True, ReferenceStyle:=xlR1C1) _ & "," & mycolumns.Address(external:=True, ReferenceStyle:=xlR1C1) _ & ")" If you're creating the formula, you want to build the string that looks like a formula. If you wanted the code to evaluate that condition, then you'd use application.worksheetfunction.sumif(). steph wrote: Hi, I'm having problems defining my sumif-formula. It returns a runtime error 424 and I don't know how to solve this problem. The formula is searching in a dynamic range and has to sum the values in a number of specific columns when the condition is met. Enclosed you will find the part of the macro which is returning the error. Sub cmdok_click() Dim rij As Integer Dim myrange As Range Dim myselection As Range Dim mycolumns As Range Dim referencepoint As Range For l = 12 To 23 Set referencepoint = Sheets("maaltijdcheques").Cells(3, l) With Worksheets("globaal uuroverzicht") Set mycolumns = .Range("V:AB") End With With Worksheets("globaal uuroverzicht") Set myresult.FormulaR1C1 = Application.WorksheetFunction.SumIf(myrange, " & referencepoint.Address(external:=True, ReferenceStyle:=xlR1C1) & ", mycolumns) End With Cells(d, l).Formula = myresult / (Worksheets("data").R15C2 + (1 / 2)) Next many thanks ! steph -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
runtime error 424
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 ) Sub cmdok_click() Dim rij As Integer Dim myrange As Range Dim myrangeoveruren As Range Dim myselection As Range Dim mycolumns As Range Dim referencepoint As Range Dim data As Range Dim myresult As String Sheets("uuroverzicht").Select Sheets("uuroverzicht").Cells(4, 1).Select Selection.End(xlDown).Select ActiveCell.Offset(1, 0).Range("A1").Select For a = 4 To 100 If Sheets("uuroverzicht").Cells(a, 1) = ActiveCell Then Sheets("uuroverzicht").Cells(a, 1).Value = txtSDnr.Text Sheets("uuroverzicht").Cells(a, 2).Value = txtvoornaam.Text Sheets("uuroverzicht").Cells(a, 3).Value = txtachternaam.Text Sheets("uuroverzicht").Cells(a, 4).Value = txtgeboortedatum.Text Sheets("uuroverzicht").Cells(a, 5).Value = txtindienst.Text Sheets("uuroverzicht").Cells(a, 7).Value = txtABM.Text Sheets("uuroverzicht").Cells(a, 8).Value = txtMF.Text Sheets("uuroverzicht").Cells(a, 9).Value = cboafdeling.Text Sheets("uuroverzicht").Cells(a, 10).Value = cbowerkregime End If Next Sheets("globaal uuroverzicht").Select Sheets("globaal uuroverzicht").Cells(6, 1).Select Selection.End(xlDown).Select ActiveCell.Offset(1, 0).Range("A1").Select For f = 6 To 800 If Sheets("globaal uuroverzicht").Cells(f, 1) = ActiveCell Then Sheets("globaal uuroverzicht").Cells(f, 1) = txtSDnr Sheets("globaal uuroverzicht").Cells(f, 2) = txtvoornaam Sheets("globaal uuroverzicht").Cells(f, 3) = txtachternaam Sheets("globaal uuroverzicht").Cells(f, 4) = txtgeboortedatum Sheets("globaal uuroverzicht").Cells(f, 5) = txtindienst Sheets("globaal uuroverzicht").Cells(f, 7) = txtABM Sheets("globaal uuroverzicht").Cells(f, 8) = txtMF Sheets("globaal uuroverzicht").Cells(f, 9) = cboafdeling Sheets("globaal uuroverzicht").Cells(f, 10) = cbowerkregime ActiveCell.EntireRow.Font.Bold = True ActiveCell.EntireRow.Insert shift:=xlDown ActiveCell.Offset(0, 0) = txtSDnr ActiveCell.Offset(0, 1) = "december" ActiveCell.Offset(0, 36).Font.ColorIndex = 3 myfirstcell = ActiveCell.Offset(0, 32).Address mylastcell = ActiveCell.Offset(0, 35).Address ActiveCell.Offset(0, 36).Formula = "=sum(" & myfirstcell & ":" & mylastcell & ")" ActiveCell.EntireRow.Font.Bold = False ActiveCell.EntireRow.Group ActiveCell.EntireRow.Insert shift:=xlDown ActiveCell.Offset(0, 0) = txtSDnr ActiveCell.Offset(0, 1) = "november" ActiveCell.Offset(0, 36).Font.ColorIndex = 3 myfirstcell = ActiveCell.Offset(0, 32).Address mylastcell = ActiveCell.Offset(0, 35).Address ActiveCell.Offset(0, 36).Formula = "=sum(" & myfirstcell & ":" & mylastcell & ")" ActiveCell.EntireRow.Font.Bold = False ActiveCell.EntireRow.Group ActiveCell.EntireRow.Insert shift:=xlDown ActiveCell.Offset(0, 0) = txtSDnr ActiveCell.Offset(0, 1) = "oktober" ActiveCell.Offset(0, 36).Font.ColorIndex = 3 myfirstcell = ActiveCell.Offset(0, 32).Address mylastcell = ActiveCell.Offset(0, 35).Address ActiveCell.Offset(0, 36).Formula = "=sum(" & myfirstcell & ":" & mylastcell & ")" ActiveCell.EntireRow.Font.Bold = False ActiveCell.EntireRow.Group ActiveCell.EntireRow.Insert shift:=xlDown ActiveCell.Offset(0, 0) = txtSDnr ActiveCell.Offset(0, 1) = "september" ActiveCell.Offset(0, 36).Font.ColorIndex = 3 myfirstcell = ActiveCell.Offset(0, 32).Address mylastcell = ActiveCell.Offset(0, 35).Address ActiveCell.Offset(0, 36).Formula = "=sum(" & myfirstcell & ":" & mylastcell & ")" ActiveCell.EntireRow.Font.Bold = False ActiveCell.EntireRow.Group ActiveCell.EntireRow.Insert shift:=xlDown ActiveCell.Offset(0, 0) = txtSDnr ActiveCell.Offset(0, 1) = "augustus" ActiveCell.Offset(0, 36).Font.ColorIndex = 3 myfirstcell = ActiveCell.Offset(0, 32).Address mylastcell = ActiveCell.Offset(0, 35).Address ActiveCell.Offset(0, 36).Formula = "=sum(" & myfirstcell & ":" & mylastcell & ")" ActiveCell.EntireRow.Font.Bold = False ActiveCell.EntireRow.Group ActiveCell.EntireRow.Insert shift:=xlDown ActiveCell.Offset(0, 0) = txtSDnr ActiveCell.Offset(0, 1) = "juli" ActiveCell.Offset(0, 36).Font.ColorIndex = 3 myfirstcell = ActiveCell.Offset(0, 32).Address mylastcell = ActiveCell.Offset(0, 35).Address ActiveCell.Offset(0, 36).Formula = "=sum(" & myfirstcell & ":" & mylastcell & ")" ActiveCell.EntireRow.Font.Bold = False ActiveCell.EntireRow.Group ActiveCell.EntireRow.Insert shift:=xlDown ActiveCell.Offset(0, 0) = txtSDnr ActiveCell.Offset(0, 1) = "juni" ActiveCell.Offset(0, 36).Font.ColorIndex = 3 myfirstcell = ActiveCell.Offset(0, 32).Address mylastcell = ActiveCell.Offset(0, 35).Address ActiveCell.Offset(0, 36).Formula = "=sum(" & myfirstcell & ":" & mylastcell & ")" ActiveCell.EntireRow.Font.Bold = False ActiveCell.EntireRow.Group ActiveCell.EntireRow.Insert shift:=xlDown ActiveCell.Offset(0, 0) = txtSDnr ActiveCell.Offset(0, 1) = "mei" ActiveCell.Offset(0, 36).Font.ColorIndex = 3 myfirstcell = ActiveCell.Offset(0, 32).Address mylastcell = ActiveCell.Offset(0, 35).Address ActiveCell.Offset(0, 36).Formula = "=sum(" & myfirstcell & ":" & mylastcell & ")" ActiveCell.EntireRow.Font.Bold = False ActiveCell.EntireRow.Group ActiveCell.EntireRow.Insert shift:=xlDown ActiveCell.Offset(0, 0) = txtSDnr ActiveCell.Offset(0, 1) = "april" ActiveCell.Offset(0, 36).Font.ColorIndex = 3 myfirstcell = ActiveCell.Offset(0, 32).Address mylastcell = ActiveCell.Offset(0, 35).Address ActiveCell.Offset(0, 36).Formula = "=sum(" & myfirstcell & ":" & mylastcell & ")" ActiveCell.EntireRow.Font.Bold = False ActiveCell.EntireRow.Group ActiveCell.EntireRow.Insert shift:=xlDown ActiveCell.Offset(0, 0) = txtSDnr ActiveCell.Offset(0, 1) = "maart" ActiveCell.Offset(0, 36).Font.ColorIndex = 3 myfirstcell = ActiveCell.Offset(0, 32).Address mylastcell = ActiveCell.Offset(0, 35).Address ActiveCell.Offset(0, 36).Formula = "=sum(" & myfirstcell & ":" & mylastcell & ")" ActiveCell.EntireRow.Font.Bold = False ActiveCell.EntireRow.Group ActiveCell.EntireRow.Insert shift:=xlDown ActiveCell.Offset(0, 0) = txtSDnr ActiveCell.Offset(0, 1) = "februari" ActiveCell.Offset(0, 36).Font.ColorIndex = 3 myfirstcell = ActiveCell.Offset(0, 32).Address mylastcell = ActiveCell.Offset(0, 35).Address ActiveCell.Offset(0, 36).Formula = "=sum(" & myfirstcell & ":" & mylastcell & ")" ActiveCell.EntireRow.Font.Bold = False ActiveCell.EntireRow.Group ActiveCell.EntireRow.Insert shift:=xlDown ActiveCell.Offset(0, 0) = txtSDnr ActiveCell.Offset(0, 1) = "januari" ActiveCell.Offset(0, 36).Font.ColorIndex = 3 myfirstcell = ActiveCell.Offset(0, 32).Address mylastcell = ActiveCell.Offset(0, 35).Address ActiveCell.Offset(0, 36).Formula = "=sum(" & myfirstcell & ":" & mylastcell & ")" ActiveCell.EntireRow.Font.Bold = False ActiveCell.EntireRow.Group ActiveCell.EntireRow.Insert shift:=xlDown ActiveCell.Offset(0, 0) = txtSDnr ActiveCell.EntireRow.Font.Bold = False ActiveCell.EntireRow.Group W = ActiveCell.Offset(12, 0).Row y = ActiveCell.Row v = InputBox("Wat is het saldo jaarlijks verlof ?") Sheets("globaal uuroverzicht").Cells(f, 33) = v Sheets("globaal uuroverzicht").Cells(f, 33).Font.ColorIndex = 3 t = InputBox("Wat is he saldo overgedragen verlof?") Sheets("globaal uuroverzicht").Cells(f, 34) = t Sheets("globaal uuroverzicht").Cells(f, 34).Font.ColorIndex = 3 u = InputBox("Wat is het saldo ancienniteitsverlof ?") Sheets("globaal uuroverzicht").Cells(f, 35) = u Sheets("globaal uuroverzicht").Cells(f, 35).Font.ColorIndex = 3 t = InputBox("Wat is het saldo betaalde feestdagen ?") Sheets("globaal uuroverzicht").Cells(f, 36) = t Sheets("globaal uuroverzicht").Cells(f, 36).Font.ColorIndex = 3 s = InputBox("Wat is het saldo overuren ?") Sheets("globaal uuroverzicht").Cells(f, 38) = s Sheets("globaal uuroverzicht").Cells(f, 38).Font.ColorIndex = 3 myfirstcell = Sheets("globaal uuroverzicht").Cells(f, 33).Address mylastcell = Sheets("globaal uuroverzicht").Cells(f, 36).Address Sheets("globaal uuroverzicht").Cells(f, 37).Formula = "=sum(" & myfirstcell & " : " & mylastcell & " ) " Sheets("globaal uuroverzicht").Cells(f, 37).Font.Bold = True Sheets("globaal uuroverzicht").Cells(f, 37).Font.ColorIndex = 3 For z = 11 To 31 myfirstcell = Sheets("globaal uuroverzicht").Cells(W, z).Address mylastcell = Sheets("globaal uuroverzicht").Cells(y, z).Address Sheets("globaal uuroverzicht").Cells(f + 13, z).Formula = "=sum(" & myfirstcell & " :" & mylastcell & " )" Sheets("globaal uuroverzicht").Cells(f + 13, z).Font.Bold = True myfirstcell = Sheets("globaal uuroverzicht").Cells(f, 37).Address mylastcell = Sheets("globaal uuroverzicht").Cells(f + 13, 19).Address Sheets("globaal uuroverzicht").Cells(f + 13, 37).Formula = "=(" & myfirstcell & " - " & mylastcell & ")" Sheets("globaal uuroverzicht").Cells(f + 13, 37).Font.Bold = True Sheets("globaal uuroverzicht").Cells(f + 13, 39) = Sheets("globaal uuroverzicht").Cells(f, 38) + Sheets("globaal uuroverzicht").Cells(f + 13, 26) + Sheets("globaal uuroverzicht").Cells(f + 13, 27) + Sheets("globaal uuroverzicht").Cells(f + 13, 28) + Sheets("globaal uuroverzicht").Cells(f + 13, 29) + Sheets("globaal uuroverzicht").Cells(f + 13, 30) - Sheets("globaal uuroverzicht").Cells(f + 13, 31) Sheets("globaal uuroverzicht").Cells(f + 13, 39).Font.Bold = True mycell = Sheets("globaal uuroverzicht").Cells(f + 13, 37).Address i = Sheets("globaal uuroverzicht").Cells(f, z).Row j = Sheets("globaal uuroverzicht").Cells(f + 12, 37).Row With Worksheets("globaal uuroverzicht") Set myrange = .Range(Cells(i, 2), Cells(j, 2)) Set mycolumns = .Range(Cells(i, 22), Cells(j, 28)) Set myrangeoveruren = .Range(Cells(i, 2), Cells(j, 36)) End With Rows(i - 2).Select Selection.Copy Range(Rows(i), Rows(j)).Select Selection.PasteSpecial Paste:=xlPasteFormats, operation:=xlNone, skipblanks:=False, Transpose:=False Application.CutCopyMode = False Next Cells(6, 1).Select End If Next Sheets("maaltijdcheques").Select Sheets("maaltijdcheques").Cells(4, 1).Select Selection.End(xlDown).Select ActiveCell.Offset(1, 0).Range("A1").Select For d = 4 To 60 If Sheets("maaltijdcheques").Cells(d, 1) = ActiveCell Then Sheets("maaltijdcheques").Cells(d, 1).Value = txtSDnr.Text Sheets("maaltijdcheques").Cells(d, 2).Value = txtvoornaam.Text Sheets("maaltijdcheques").Cells(d, 3) = txtachternaam.Text Sheets("maaltijdcheques").Cells(d, 4) = txtgeboortedatum.Text Sheets("maaltijdcheques").Cells(d, 5) = txtindienst.Text Sheets("maaltijdcheques").Cells(d, 7) = txtABM.Text Sheets("maaltijdcheques").Cells(d, 8) = txtMF.Text Sheets("maaltijdcheques").Cells(d, 9) = cboafdeling.Text Sheets("maaltijdcheques").Cells(d, 10) = cbowerkregime.Text For l = 12 To 23 Set referencepoint = Sheets("maaltijdcheques").Cells(3, l) Set data = Sheets("data").Cells(15, 2) 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 Sheets("maaltijdcheques").Cells(d, l).FormulaR1C1 = myresultStr <====== runtime errorcode 1004 !! Next End If Next Sheets("uuroverzicht").Select With Worksheets("uuroverzicht") Set myselection = .Range("$C$2") End With Sheets("uuroverzicht").Cells(10, 32).Select Selection.End(xlDown).Select ActiveCell.Offset(1, 0).Formula = "='globaal uuroverzicht'! " & mycell & "" ActiveCell.Offset(1, 0).Font.ColorIndex = 10 ActiveCell.Offset(1, 1).FormulaR1C1 = "=SUM(vlookup(" & myselection.Address(external:=True, ReferenceStyle:=xlR1C1) & "," & myrangeoveruren.Address(external:=True, ReferenceStyle:=xlR1C1) & ",37,FALSE),vlookup(" & myselection.Address(external:=True, ReferenceStyle:=xlR1C1) & ", " & myrangeoveruren.Address(external:=True, ReferenceStyle:=xlR1C1) & ", 38, false))" nieuw_personeelslid.Hide End Sub "Dave Peterson" schreef: I think you're going to have to post the snippet of code that isn't working. steph wrote: Yes, that is what I also thought but it seems not to be working. I receive an errorcode 1004, allthough the myresultStr is defined. Can it not be something because myresultStr is a string and I need to attach the formula to a cell ? Any ideas ? I'm so close finalizing the macro but this part seems to bother me a lot. Thanks stephanie "Dave Peterson" schreef: worksheets("some sheet name here").cells(1,"D").formular1c1 _ = myresultStr Maybe???? steph wrote: Hi, still no luck ! The next problem I'm facing is that the string has to be linked to a cell. The myresultStr has been defined but now I need to have this result in cell (d,l). How is this possible ? Steph "Dave Peterson" schreef: When you use myResult.formular1c1, then myresult has to be a range--not just a range object (that might not be set). If you want to just hold that string, then declare a string and use that: dim myFormulaR1C1Str as string myformulaR1C1Str = "....." steph wrote: I don't understand why this have to be included. The myresult is only a variable which will be used later to define a cell. It does not need to be referenced to a cell (unless this is why it is not working?) So far I've made following adaptions : Sub cmdok_click() Dim rij As Integer Dim myrange As Range Dim myselection As Range Dim mycolumns As Range Dim referencepoint As Range Dim myresult As Range For z = 11 To 31 myfirstcell = Sheets("globaal uuroverzicht").Cells(W, z).Address mylastcell = Sheets("globaal uuroverzicht").Cells(y, z).Address Sheets("globaal uuroverzicht").Cells(f + 13, z).Formula = "=sum(" & myfirstcell & " :" & mylastcell & " )" Sheets("globaal uuroverzicht").Cells(f + 13, z).Font.Bold = True myfirstcell = Sheets("globaal uuroverzicht").Cells(f, 37).Address mylastcell = Sheets("globaal uuroverzicht").Cells(f + 13, 19).Address Sheets("globaal uuroverzicht").Cells(f + 13, 37).Formula = "=(" & myfirstcell & " - " & mylastcell & ")" Sheets("globaal uuroverzicht").Cells(f + 13, 37).Font.Bold = True Sheets("globaal uuroverzicht").Cells(f + 13, 39) = Sheets("globaal uuroverzicht").Cells(f, 38) + Sheets("globaal uuroverzicht").Cells(f + 13, 26) + Sheets("globaal uuroverzicht").Cells(f + 13, 27) + Sheets("globaal uuroverzicht").Cells(f + 13, 28) + Sheets("globaal uuroverzicht").Cells(f + 13, 29) + Sheets("globaal uuroverzicht").Cells(f + 13, 30) - Sheets("globaal uuroverzicht").Cells(f + 13, 31) Sheets("globaal uuroverzicht").Cells(f + 13, 39).Font.Bold = True mycell = Sheets("globaal uuroverzicht").Cells(f + 13, 37).Address i = Sheets("globaal uuroverzicht").Cells(f, z).Row j = Sheets("globaal uuroverzicht").Cells(f + 12, 37).Row With Worksheets("globaal uuroverzicht") Set myrange = .Range(Cells(i, 2), Cells(j, 2)) Set mycolumns = .Range(Cells(i, 22), Cells(j, 28)) End With Rows(i - 2).Select Selection.Copy Range(Rows(i), Rows(j)).Select Selection.PasteSpecial Paste:=xlPasteFormats, operation:=xlNone, skipblanks:=False, Transpose:=False Application.CutCopyMode = False Next Cells(6, 1).Select End If Next Sheets("maaltijdcheques").Select Sheets("maaltijdcheques").Cells(4, 1).Select Selection.End(xlDown).Select ActiveCell.Offset(1, 0).Range("A1").Select For d = 4 To 60 If Sheets("maaltijdcheques").Cells(d, 1) = ActiveCell Then Sheets("maaltijdcheques").Cells(d, 1).Value = txtSDnr.Text Sheets("maaltijdcheques").Cells(d, 2).Value = txtvoornaam.Text Sheets("maaltijdcheques").Cells(d, 3) = txtachternaam.Text Sheets("maaltijdcheques").Cells(d, 4) = txtgeboortedatum.Text Sheets("maaltijdcheques").Cells(d, 5) = txtindienst.Text Sheets("maaltijdcheques").Cells(d, 7) = txtABM.Text Sheets("maaltijdcheques").Cells(d, 8) = txtMF.Text Sheets("maaltijdcheques").Cells(d, 9) = cboafdeling.Text Sheets("maaltijdcheques").Cells(d, 10) = cbowerkregime.Text For l = 12 To 23 Set referencepoint = Sheets("maaltijdcheques").Cells(3, l) MsgBox referencepoint.Address(external:=True) MsgBox myrange.Address(external:=True) MsgBox mycolumns.Address(external:=True) With Worksheets("globaal uuroverzicht") Set myresult.FormulaR1C1 = "=SumIf(" & myrange.Address(external:=True, ReferenceStyle:=xlR1C1) & ", " & referencepoint.Address(external:=True, ReferenceStyle:=xlR1C1) & "," & mycolumns.Address(external:=True, ReferenceStyle:=xlR1C1) & ")" MsgBox myresult.Address(external:=True) End With Cells(d, l).Formula = myresult / Worksheets("data").R15C2 + (1 / 2) Next End If Next This is still not the complete macro, but I suppose this part will give you more information on what I am doing possibly wrong. The problem is that I have on different sheets variable ranges which need to be linked to each other using formulas. Hope this will give you more information to find a possible solution. Steph "Dave Peterson" schreef: What range should myResult point to? Make sure you include that in your code: set myresult = worksheets("globaal uuroverzicht").range("a1") or something like that. steph wrote: Hi, The messageboxes show that my defined ranges are well set. referencepoint =[test.xls]maaltijdcheques!$L$3 myrange = [test.xls]globaal uuroverzicht!$B$565:$B$577 mycolumns = [test.xls]globaal uuroverzicht!$V$565:$AB$577 but for myresult is still get the runtime errorcode 91 Anymore thoughts of what could be wrong Thanks stephanie "Dave Peterson" schreef: I think I'd add some msgboxes to see what's happening: msgbox myrange.address(external:=true) msgbox referencepoint.address(external:=true) msgbox mycolumns.address(external:=true) msgbox myresult.address(external:=true) I'm guessing that one of these variables isn't set to a real range. steph wrote: Hi Dave The myrange has been defined and used in another part of the macro. When adapting the formula to yours, i receive another errorcode being runtime error 91 (object variable or with block variable not set). I've defined myresult as range and I have omitted the with ... end with. However when I include again the with ... end with, i still get the same errorcode. "Dave Peterson" schreef: You declared myRange and used myResult--but never set them to anything. And when you're assigning a string to that .formular1c1, the you shouldn't use "Set". And I would think that you would want something like: myResult.FormulaR1C1 = "=sumif(" & _ myrange.Address(external:=True, ReferenceStyle:=xlR1C1) _ & "," _ & referencepoint.Address(external:=True, ReferenceStyle:=xlR1C1) _ & "," & mycolumns.Address(external:=True, ReferenceStyle:=xlR1C1) _ & ")" If you're creating the formula, you want to build the string that looks like a formula. If you wanted the code to evaluate that condition, then you'd use application.worksheetfunction.sumif(). steph wrote: Hi, I'm having problems defining my sumif-formula. It returns a runtime error 424 and I don't know how to solve this problem. The formula is searching in a dynamic range and has to sum the values in a number of specific columns when the condition is met. Enclosed you will find the part of the macro which is returning the error. Sub cmdok_click() Dim rij As Integer Dim myrange As Range Dim myselection As Range Dim mycolumns As Range Dim referencepoint As Range For l = 12 To 23 Set referencepoint = Sheets("maaltijdcheques").Cells(3, l) With Worksheets("globaal uuroverzicht") Set mycolumns = .Range("V:AB") End With With Worksheets("globaal uuroverzicht") Set myresult.FormulaR1C1 = Application.WorksheetFunction.SumIf(myrange, " & referencepoint.Address(external:=True, ReferenceStyle:=xlR1C1) & ", mycolumns) End With Cells(d, l).Formula = myresult / (Worksheets("data").R15C2 + (1 / 2)) Next many thanks ! steph -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
runtime error 424
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 |
runtime error 424
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 |
runtime error 424
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 |
runtime error 424
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 |
runtime error 424
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 |
runtime error 424
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 |
runtime error 424
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 |
runtime error 424
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 |
runtime error 424
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 |
runtime error 424
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 |
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 |
| All times are GMT +1. The time now is 10:10 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com