summit is mis-spelled
>________________________________
>From: BarryN <brnaugle@yahoo.com>
>To: ExcelVBA@yahoogroups.com
>Sent: Wednesday, 2 November 2011, 14:44
>Subject: [ExcelVBA] Re: Assigning value to a cell
>
>
>
>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]
>>
>
>
>
>
>
[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