no protection....
if i run it in a sub procedure it works fine, but i'm running it as a function procedure.
This works:
Public Sub sumit2()
Dim d1 As Date
Dim d2 As Date
Dim cat As String
Dim sumitanswer As Double
Dim criteria2 As Range
Dim rngN03 As Range
d1 = "1 / 1 / 2011"
d2 = "6 / 30 / 2011"
cat = "Mortgage"
Set rngN03 = Range("rngn03")
Set criteria2 = Sheets("Adjustments").Range(Cells(1,3), Cells(3,12))
Sheets("Adjustments").Cells(2, 3) = ">=" & d1
Sheets("Adjustments").Cells(3, 3) = "<=" & d2
Sheets("Adjustments").Cells(2, 8) = cat
sumitanswer = WorksheetFunction.DSum(rngN03, "Amt", criteria2)
End Sub
This does not:
Public Function sumit(Dat1 As Date, dat2 As Date, cat As String) As Double
Dim criteria As Range
Dim rngN03 As Range
Set rngN03 = Range("rngn03")
Set criteria = Sheets("Adjustments").Range(Cells(1, 3), Cells(3, 12))
Sheets("Adjustments").Cells(3, 2) = ">=" & Dat1
Sheets("Adjustments").Cells(3, 3) = "<=" & dat2
Sheets("Adjustments").Cells(2, 8) = cat
summit = WorksheetFunction.DSum(rngN03, "Amt", criteria)
End Function
--- In ExcelVBA@yahoogroups.com, Derek Turner <g4swy@...> wrote:
>
> Protection ?
>
>
>
> >________________________________
> >From: "david.smart@..." <david.smart@...>
> >To: ExcelVBA@yahoogroups.com
> >Sent: Wednesday, 2 November 2011, 2:04
> >Subject: RE: [ExcelVBA] Re: Assigning value to a cell
> >
> >
> >Â
> >Can you post more of the code. Certainly, this code works fine for me:
> >
> >Option Explicit
> >
> >Sub x()
> >Dim D1 As Date
> >D1 = Now()
> >Sheets("Adjustments").Cells(3, 2) = ">=" & D1
> >End Sub
> >
> >
> >Regards, Dave S
> >
> >________________________________
> >
> >From: ExcelVBA@yahoogroups.com [mailto:ExcelVBA@yahoogroups.com] On
> >Behalf Of BarryN
> >Sent: Wednesday, 2 November 2011 11:49
> >To: ExcelVBA@yahoogroups.com
> >Subject: [ExcelVBA] Re: Assigning value to a cell
> >
> >Thanks Dave - but this is in a function procedure where D1 and D2 are
> >inputs defined as Date. I've tried adding "Range", "Value" etc without
> >success and suspect there is something in the references or elsewhere
> >messing this up.
> >
> >Barry
> >
> >--- In ExcelVBA@yahoogroups.com <mailto:ExcelVBA%40yahoogroups.com> ,
> ><david.smart@> wrote:
> >>
> >> Firstly ... why it disappears rather than throwing an error ... I'm
> >> assuming that this line of code is in an event handler, e.g. a change
> >> event. When Excel encounters a problem in an event handler, it
> >> terminates the event without further notification.
> >>
> >>
> >> > Sheets("Adjustments").cells(3,2)=">=" & D1
> >>
> >> This requires that you have a sheet called Adjustments and a variable
> >> called D1. I suspect you actually want to refer to the cell D1, which
> >> will require a Range("D1"), possibly also with a sheet qualifier.
> >>
> >> Also, please use .Value or .Formula as appropriate, so that Excel
> >knows
> >> where to put/get the information, so
> >>
> >> > Sheets("Adjustments").cells(3,2).Value = ">=" & Range("D1").Value
> >>
> >> Also, if D1 has a date in it, then you probably want to convert it to
> >a
> >> date string before you append it to the >=. Otherwise you'll just get
> >> the numeric value of the date, I suspect.
> >>
> >>
> >> Regards, Dave S
> >>
> >>
> >>
> >> ________________________________
> >>
> >> From: ExcelVBA@yahoogroups.com <mailto:ExcelVBA%40yahoogroups.com>
> >[mailto:ExcelVBA@yahoogroups.com <mailto:ExcelVBA%40yahoogroups.com> ]
> >On
> >> Behalf Of BarryN
> >> Sent: Wednesday, 2 November 2011 01:16
> >> To: ExcelVBA@yahoogroups.com <mailto:ExcelVBA%40yahoogroups.com>
> >> Subject: [ExcelVBA] Assigning value to a cell
> >>
> >>
> >>
> >>
> >> I've done this dozens of times but when I step through a function to
> >> this line of code:
> >>
> >> Sheets("Adjustments").cells(3,2)=">=" & D1
> >>
> >> where D1 is a date, the yellow line disappears and the function stops.
> >>
> >> Obvious solution but I can't see it - thanks!
> >>
> >>
> >>
> >>
> >>
> >> ----------
> >>
> >> Visit our website at http://www.ubs.com
> >>
> >> This message contains confidential information and is intended only
> >> for the individual named. If you are not the named addressee you
> >> should not disseminate, distribute or copy this e-mail. Please
> >> notify the sender immediately by e-mail if you have received this
> >> e-mail by mistake and delete this e-mail from your system.
> >>
> >> E-mails are not encrypted and cannot be guaranteed to be secure or
> >> error-free as information could be intercepted, corrupted, lost,
> >> destroyed, arrive late or incomplete, or contain viruses. The sender
> >> therefore does not accept liability for any errors or omissions in the
> >
> >> contents of this message which arise as a result of e-mail
> >transmission.
> >> If verification is required please request a hard-copy version. This
> >> message is provided for informational purposes and should not be
> >> construed as a solicitation or offer to buy or sell any securities
> >> or related financial instruments.
> >>
> >>
> >> UBS reserves the right to retain all messages. Messages are protected
> >> and accessed only in legally justified cases.
> >>
> >> [Non-text portions of this message have been removed]
> >>
> >
> >----------
> >
> >Visit our website at http://www.ubs.com
> >
> >This message contains confidential information and is intended only
> >for the individual named. If you are not the named addressee you
> >should not disseminate, distribute or copy this e-mail. Please
> >notify the sender immediately by e-mail if you have received this
> >e-mail by mistake and delete this e-mail from your system.
> >
> >E-mails are not encrypted and cannot be guaranteed to be secure or
> >error-free as information could be intercepted, corrupted, lost,
> >destroyed, arrive late or incomplete, or contain viruses. The sender
> >therefore does not accept liability for any errors or omissions in the
> >contents of this message which arise as a result of e-mail transmission.
> >If verification is required please request a hard-copy version. This
> >message is provided for informational purposes and should not be
> >construed as a solicitation or offer to buy or sell any securities
> >or related financial instruments.
> >
> >UBS reserves the right to retain all messages. Messages are protected
> >and accessed only in legally justified cases.
> >
> >[Non-text portions of this message have been removed]
> >
> >
> >
> >
> >
>
> [Non-text portions of this message have been removed]
>
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar