Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...
MS Office Forum / Excel / Programming / January 2006

Tip: Looking for answers? Try searching our database.

runtime error 424

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
steph - 10 Jan 2006 14:48 GMT
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
Reply to this Message
JNW - 10 Jan 2006 20:26 GMT
I could be wrong but I don't see where you have defined "myrange".  That
might be it.

> Hi,
>
[quoted text clipped - 27 lines]
>
> steph
Reply to this Message
Dave Peterson - 10 Jan 2006 21:34 GMT
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().

> Hi,
>
[quoted text clipped - 27 lines]
>
> steph

Signature

Dave Peterson

Reply to this Message
steph - 11 Jan 2006 08:27 GMT
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.
>
[quoted text clipped - 47 lines]
> >
> > steph
Reply to this Message
Dave Peterson - 11 Jan 2006 14:38 GMT
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.

> Hi Dave
>
[quoted text clipped - 61 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message
steph - 12 Jan 2006 10:32 GMT
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:
>
[quoted text clipped - 70 lines]
> > >
> > > Dave Peterson
Reply to this Message
Dave Peterson - 12 Jan 2006 15:20 GMT
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,
>
[quoted text clipped - 90 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message
steph - 13 Jan 2006 08:32 GMT
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?
>
[quoted text clipped - 96 lines]
> > >
> > > Dave Peterson
Reply to this Message
Dave Peterson - 13 Jan 2006 16:02 GMT
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
[quoted text clipped - 189 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message
steph - 16 Jan 2006 10:16 GMT
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).
[quoted text clipped - 198 lines]
> > >
> > > Dave Peterson
Reply to this Message
Dave Peterson - 16 Jan 2006 15:32 GMT
worksheets("some sheet name here").cells(1,"D").formular1c1 _
  = myresultStr

Maybe????

> Hi,
>
[quoted text clipped - 212 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message
steph - 17 Jan 2006 07:36 GMT
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
[quoted text clipped - 217 lines]
> > >
> > > Dave Peterson
Reply to this Message
Dave Peterson - 17 Jan 2006 15:59 GMT
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
[quoted text clipped - 233 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message
steph - 17 Jan 2006 16:12 GMT
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.
>
[quoted text clipped - 235 lines]
> > >
> > > Dave Peterson
Reply to this Message
Dave Peterson - 17 Jan 2006 16:44 GMT
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 )

<<snipped>>
Reply to this Message
steph - 18 Jan 2006 08:19 GMT
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:
>
[quoted text clipped - 32 lines]
> >
> <<snipped>>
Reply to this Message
Dave Peterson - 18 Jan 2006 15:47 GMT
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.

> 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
[quoted text clipped - 45 lines]
> > >
> > <<snipped>>

Signature

Dave Peterson

Reply to this Message
steph - 20 Jan 2006 08:11 GMT
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.
>
[quoted text clipped - 50 lines]
> > > >
> > > <<snipped>>
Reply to this Message
Dave Peterson - 20 Jan 2006 16:51 GMT
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.

> 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
[quoted text clipped - 69 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message
steph - 23 Jan 2006 08:52 GMT
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?
>
[quoted text clipped - 83 lines]
> > >
> > > Dave Peterson
Reply to this Message
Dave Peterson - 23 Jan 2006 15:10 GMT
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?

> Hi,
>
[quoted text clipped - 98 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message
steph - 23 Jan 2006 16:02 GMT
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.
>
[quoted text clipped - 103 lines]
> > >
> > > Dave Peterson
Reply to this Message
Dave Peterson - 23 Jan 2006 18:10 GMT
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.

> No, I didn't.  I just added = before the INT(SUMIF("...))"
> This is what I have now :
[quoted text clipped - 126 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message
steph - 24 Jan 2006 07:39 GMT
Hi Dave,

It worked ! Thank you so much for all you time and effort !

steph

"Dave Peterson" schreef:

> VBA is USA centric.
>
[quoted text clipped - 133 lines]
> > >
> > > Dave Peterson
Reply to this Message
Dave Peterson - 24 Jan 2006 15:39 GMT
Glad you got it working.

> Hi Dave,
>
[quoted text clipped - 145 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage
Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.