Formula not working because of decimal separator

[BeRniTo]

Hello again!

I'm getting the "Unable to set FormulaArray property of the Range class" error after runing the following 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?

 

[xenou]

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.

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?

 

[BeRniTo]

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!

 

[shg]

Does this help as an example?

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.

 

[BeRniTo]

Thanks shg4421! but doesn't work if timBeg is PM and timEnd is AM:

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?

 

[shg]

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

 

[BeRniTo]

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.

 

[shg]

Mon frere,

    tim[COLOR=red]Beg[/COLOR] = #8:00:00 PM#
    tim[COLOR=red]End[/COLOR] = #10:00:00 AM#

There is no time that is after 8PM and before 10AM.

 

[xenou]

    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

 

[BeRniTo]

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.