Page 1 of 3 123 LastLast
Results 1 to 10 of 26

Formula not working because of decimal separator

This is a discussion on Formula not working because of decimal separator within the Excel Questions forums, part of the Question Forums category; Hello again! I'm getting the "Unable to set FormulaArray property of the Range class" error after runing the following code: ...

  1. #1
    Board Regular
    Join Date
    Oct 2010
    Location
    Montevideo, Uruguay
    Posts
    52

    Exclamation Formula not working because of decimal separator

    Hello again!

    I'm getting the "Unable to set FormulaArray property of the Range class" error after runing the following code:
    Code:
    Sub test()
        Range("A1").Value = "05/05/1987 21:00:00"
        Dim timeCompare As Date
        timeCompare = Format("20:00:00", "hh:mm:ss;@")
        
        Range("A2").FormulaArray = "=MOD(A1,1)"
        Range("A3").FormulaArray = _
            "=IF(MOD(A1,1) >= " & CDbl(TimeValue(timeCompare)) & ",1,0)"
    End Sub
    I'm using the spanish version of Excel 2007 and the decimal separator here is the comma and not the dot. Is there a way, over VBA, to make this comparison?

  2. #2
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    14,056

    Default Re: Formula not working because of decimal separator

    More likely the "formula" you are entering in the cell is invalid. It must look the same as if you entered it by hand on the worksheet. Always view these in the immediate window or in a msgbox when testing.

    Code:
    Dim sFormula As String
    sFormula = "=IF(MOD(A1,1) >= TIMEVALUE(" & Chr(34) & timeCompare & Chr(34) & "),1,0)"
    debug.print sFormula
    Range("A3").Formula = sFormula
    Note: I see no reason for an array formula here. Also, why the ;@ at the end of the format string (hh:mm:ss;@) - I'm not sure what that does - can you educate me?

    Using: Office 2007/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  3. #3
    Board Regular
    Join Date
    Oct 2010
    Location
    Montevideo, Uruguay
    Posts
    52

    Default Re: Formula not working because of decimal separator

    You're right. I don't know how to do what I want.

    I have a dialog prompted to the user to select a range of times.
    Then, I want to put a 1 in cell A2 if the time of day in A1 is between the user-specified range of time and a 0 if not.

    The FormulaArray is because that formula is (in the original code) part of an array formula so I left it instead of changing it to .Formula, but doesn't matter in the sample code, sorry.

    An example of what I want to accomplish:

    If the date in A1 is "05/05/1987 01:00:00" and the user specifies the range that goes from "20:00:00" to "06:00:00" then the result of the formula should be 1.

    How can I do this?

    My english is aweful, sorry about that!
    Last edited by BeRniTo; Oct 7th, 2010 at 06:30 PM.

  4. #4
    shg
    shg is offline
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    13,753

    Default Re: Formula not working because of decimal separator

    Does this help as an example?

    Code:
    Sub test()
        Dim timBeg      As Date
        Dim timEnd      As Date
     
        timBeg = #8:00:00 AM#
        timEnd = #10:00:00 PM#
     
        With Range("A1")
            .Value = DateSerial(1987, 10, 20) + #9:00:00 PM#
            .NumberFormat = "dd mmm yyyy hh:mm"
     
            .Offset(1).Formula = "=mod(A1,1)"
            .Offset(1).NumberFormat = "hh:mm"
     
            .Offset(2).Formula = "=and(a2 >= --" & Format(timBeg, "\""h:mm\""") & _
                                    ", a2 <= --" & Format(timEnd, "\""h:mm\""") & ")"
        End With
    End Sub
    Date constants (#..#) are a convenient way to set date variable.

    DateSerial eliminates any ambiguity in date formats.

    The -- in the last formula coerces the string time values into numbers.
    Last edited by shg; Oct 7th, 2010 at 07:26 PM.

  5. #5
    Board Regular
    Join Date
    Oct 2010
    Location
    Montevideo, Uruguay
    Posts
    52

    Default Re: Formula not working because of decimal separator

    Thanks shg4421! but doesn't work if timBeg is PM and timEnd is AM:
    Code:
    Sub test2()
        Dim timBeg      As Date
        Dim timEnd      As Date
        timBeg = #8:00:00 PM#
        timEnd = #10:00:00 AM#
        With Range("A1")
            .Value = DateSerial(1987, 10, 20) + #9:00:00 PM#
            .NumberFormat = "dd mmm yyyy hh:mm"
            .Offset(1).Formula = "=mod(A1,1)"
            .Offset(1).NumberFormat = "hh:mm"
            .Offset(2).Formula = "=and(a2 >= --" & Format(timBeg, "\""h:mm\""") & _
                                    ", a2 <= --" & Format(timEnd, "\""h:mm\""") & ")"
        End With
    End Sub
    What should be changed?

  6. #6
    shg
    shg is offline
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    13,753

    Default Re: Formula not working because of decimal separator

    Bernito, you have had a minor brain cramp. The begin time can't be later than the end time.

  7. #7
    Board Regular
    Join Date
    Oct 2010
    Location
    Montevideo, Uruguay
    Posts
    52

    Default Re: Formula not working because of decimal separator

    Never heard of that expression, "brain cramp". I've learned something new.

    How's that? 21:00:00 is after 20:00:00 and before 10:00:00... that's what I want to evaluate.

    I have a date in A1 and want to know if it's between a range the user specify.
    Imagine you have a resto-pub and want to know how much was sold between 20:00:00 and 10:00:00 so to evaluate if it's profitable being opened.

    That's one of the objectives of the sheet I'm trying to create.
    Last edited by BeRniTo; Oct 7th, 2010 at 08:08 PM.

  8. #8
    shg
    shg is offline
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    13,753

    Default Re: Formula not working because of decimal separator

    Mon frere,

    Code:
        timBeg = #8:00:00 PM#
        timEnd = #10:00:00 AM#
    There is no time that is after 8PM and before 10AM.
    Last edited by shg; Oct 7th, 2010 at 08:15 PM.

  9. #9
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    14,056

    Default Re: Formula not working because of decimal separator

    Code:
        timBeg = #8:00:00 PM#
        timEnd = #10:00:00 AM#
    If we are crossing a date boundary it's trickier ... is that what's happening here? I hope not or it will probably give me a brain cramp too

    Using: Office 2007/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  10. #10
    Board Regular
    Join Date
    Oct 2010
    Location
    Montevideo, Uruguay
    Posts
    52

    Default Re: Formula not working because of decimal separator

    xenou, exactly!

    Let me quote myself just in case you didn't read my previous reply because shg4421 replied less than 2 minutes later.

    I have a date in A1 and want to know if it's between a range the user specified.
    Imagine you have a resto-pub and want to know how much was sold between 20:00:00 and 10:00:00 (of the next day) so to evaluate if it's profitable being opened in that period.

    That's one of the objectives of the sheet I'm trying to create.
    Hope you now understood what I'm trying to accomplish.

    Last edited by BeRniTo; Oct 7th, 2010 at 08:25 PM.

Page 1 of 3 123 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com