[ExcelVBA] Re: Assigning value to a cell

 

Thanks Paul - I usually use Options Explicit but this was such a short program there was no possible way i could screw it up ; )

Barry

--- In ExcelVBA@yahoogroups.com, Paul Schreiner <schreiner_paul@...> wrote:
>
> I agree with  Derek,
>
> I suggest you use:
> Option Explicit
> at the top of your modules.
>
> That way, all variables have to be declared.
>
> This would have caught
> summit =
> instead of
> sumit =
>  
> Paul
> -----------------------------------------
> “Do all the good you can,
> By all the means you can,
> In all the ways you can,
> In all the places you can,
> At all the times you can,
> To all the people you can,
> As long as ever you can.” - John Wesley
> -----------------------------------------
>
>
>
>
> ________________________________
> From: Derek Turner <g4swy@...>
> To: "ExcelVBA@yahoogroups.com" <ExcelVBA@yahoogroups.com>
> Sent: Thu, November 3, 2011 5:00:19 AM
> Subject: [ExcelVBA] Re: Assigning value to a cell
>
>  
>
>
> summit is mis-spelled
> >
> >
> >
> >
> >
> >
> >>________________________________
> >>From: BarryN <brnaugle@...>
> >>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]
>
>
>
>
> [Non-text portions of this message have been removed]
>

__._,_.___

Reply to sender | Reply to group | Reply via web post | Start a New Topic

Messages in this topic (13)

Recent Activity:

• New Members 4

Visit Your Group

----------------------------------
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

----------------------------------

MARKETPLACE

Stay on top of your group activity without leaving the page you're on - Get the Yahoo! Toolbar now.

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use

.

__,_._,___