runtime error 424

S

steph

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
 
D

Dave Peterson

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().
 
S

steph

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:
 
D

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.
 
S

steph

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:
 
D

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.
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:
Click to expand...
 
S

steph

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.
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
Click to expand...
Click to expand...
Click to expand...
 
D

Dave Peterson

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 = "....."

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.
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
Click to expand...
Click to expand...
Click to expand...
 
S

steph

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 = "....."

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
Click to expand...
Click to expand...
Click to expand...
 
D

Dave Peterson

worksheets("some sheet name here").cells(1,"D").formular1c1 _
= myresultStr

Maybe????
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 = "....."

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
Click to expand...
Click to expand...
Click to expand...
 
S

steph

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????
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
Click to expand...
Click to expand...
Click to expand...
 
D

Dave Peterson

I think you're going to have to post the snippet of code that isn't working.
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????
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
Click to expand...
Click to expand...
Click to expand...
 
S

steph

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.
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
Click to expand...
Click to expand...
Click to expand...
 
D

Dave Peterson

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.


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 )
Click to expand...
<<snipped>>
 
S

steph

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:
 
D

Dave Peterson

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.
 
S

steph

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:
 
D

Dave Peterson

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.
 
S

steph

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:
 

Ask a Question

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

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

Ask a Question

Similar Threads

runtime Error '424' using Application.Selection 4
Nested Loop Offset Glitch - Object required (Error 424) 1
Macro: MonthName Function, runtime error 424 4
Runtime error 424 - Object required 1
runtime error 9 -- subscript out of range 12
Runtime Error 1
Runtime Error 1004 when runing Sub() from btnClick 2
Excel VBA: Runtime error 424, Object required 1

Top