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