Formula not working because of decimal separator

BeRniTo

Board Regular
Joined
Oct 5, 2010
Messages
52
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? :(
 
Okay. Well, its merely a question of the points on the boundaries of the time interval. I'll have to look again later - times are not my best suit but we should be able to work it out. Really, it should only be a matter of getting the less than/greater than/equal to operators worked out properly. If we are missing dates where one is equal to another, that's why.

I though of two glitches in my formula logic:
1) in setting the 24 hour limit, it checks if the second date is 24 hours later than the first date, but not vice versa.
2) I'm not sure if you want to allow an exact 24 hour period - this might be something you need to decide for yourself. Pro: it would allow you to track something for one complete day. Con: a 24 hour period could be a result of dates without times - i.e, bad data entry. It really depends on how the formula is intended to be used.
 
Upvote 0

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
No, an exact 24-hour period isn't allowed. The user has another way of filtering from a date to another so he should use that instead of setting a 24-hour period of time.

I've made some tests and it seems that when the date's time is equal to either the begin or end times, the comparison MOD(C1,1) = MOD(B1,1) (or using A1 instead of B1 if date's time is equal to the end time) fails... :confused:

Try this code:
Code:
Sub test()
    Range("A1").Formula = "=--MOD(""05/05/1987 20:00:00"",1)"
    Range("B1").Formula = "=--""20:00:00"""
    Range("C1").Formula = "=(A1=B1)"

    Range("A1").NumberFormat = "0.00000000000000000000"
    Range("B1").NumberFormat = "0.00000000000000000000"
End Sub
See that the numbers of both times are different and that's the reason the result is FALSE, but... why both numbers are actually different?? :eek: :eek:

EDIT: Okay, after thinking a little I understood the reason (MOD function makes Excel to round up or trunc), so... how can we fix this?
 
Last edited:
Upvote 0
Okay, I think I did the trick!! :)

I realised that from one second to another, the number of the time is affected by its fifth decimal position so I've come with this formula:

=IF(--A1<--B1,IF(AND(TRUNC(MOD(C1,1)*100000)>=TRUNC(--A1*100000),TRUNC(MOD(C1,1)*100000)<=TRUNC(--B1*100000)),1,0),IF(O(TRUNC(MOD(C1,1)*100000)>=TRUNC(--A1*100000),TRUNC(MOD(C1,1)*100000)<=TRUNC(--B1*100000)),1,0))

It seems to be working but I'll test it in my array formula now and let you know. :)

P.S: Did I explain myself correctly in the first sentence? My english is sooo bad! :p
 
Last edited:
Upvote 0
Finally, it works perfectly!

I had to change the IF logic into math's so to use it in my array formula but worked nicely.

Thank you xenou!! :)
 
Upvote 0
Nice work - glad it's going right now :)

Cheers,
ξ
 
Upvote 0
I'm here again! :(

My workaround works but makes my macro veeeery slow when analysing a file with 32 thousand lines.
I think it isn't only that but also the .FormulaArray workaround. I've realised it takes too long to make the replacement.

No one had to search for dates like this and has a better way of doing it? :(
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top