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

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

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

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

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

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

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