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
|