Run-time error '-2147417848(80010108) : Method 'Formula' of object 'Range' failed

[Norlina Deli]

HI guys,

Thank you for reading this post. I'm facing some issue.
SO the code below works perfectly well . However when I paste more then 100 rows od data in the data column . I received an error.

The purposed of the code is to drag the formula across whenever there is new data.
.
Run-time error '-2147417848(80010108) : Method 'Formula' of object 'Range' failed.

Not sure why?

Please Login or Register  to view this content.

Cell B2 has formula =COUNTIFS(A:A,A2,D:D,"<"&D2,E:E,E2)+1
Cell C2 has formula ="Weekday "&WEEKDAY(D2,2)
Cell E2 has formula ="WEEK "&WEEKNUM(D2,2)
Cell F2 has formula =IF(B2>1,"Duplicate,"")

[:) Sixthsense :)]

Turn off the event and turn it on in the end.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[Norlina Deli]

Hi there @ sixthsense , it works.Thank you so much

[Norlina Deli]

hi there @ Sixthsense , I would like to ask your guidance, as although it work it takes so much time to calculate around half an hour just for 500 data as opposed to if I just drag the formula less then a minute . Is there a way to make it calculate more faster optimizing the code? Thanks in advance your help is very much appreciated.

[:) Sixthsense :)]

Try this...

Please Login or Register  to view this content.

[Norlina Deli]

HI there @ Sixthsense , so sorry for the late reply. I just tried the new code you have given. However, its still taking too much time just to drag the formula across the data. Did I do something wrong maybe? Thank you so much for helping me with this .

[:) Sixthsense :)]

Looping the cells takes more time here..

Is it possible to keep the formula in B2:F2?

If so, we can copy paste the B2:F2 formula in one shot and loop will be avoided.

[Norlina Deli]

ahh ic so copy paste first the formula in the B2:F2

[Norlina Deli]

I give it a go then

[:) Sixthsense :)]

Could you please tell me when the event needs to run?

Because currently its running on any change that occurs on that sheet.

[Norlina Deli]

Hi @ Sixthsense , I copy paste the formula at the first data row, but still takes time. Or maybe I didnt understand what you meant just now? So sorry I'm really new with this thus might make alot of mistakes.

[Norlina Deli]

event needs to run when new data is added. coz everyday new data will be added into the sheet .

[Norlina Deli]

just a sec, Ill attached my file so you can see how it works clearly.

[Norlina Deli]

hi @ Sixthsense , attached is the file smaple.
So the idea is every day I would have to paste about 900 rows of data . The code will calculate how many duplicates in a week and restart the duplicate ocunt every beginning of each week

[:) Sixthsense :)]

event needs to run when new data is added. coz everyday new data will be added into the sheet .

Which is the determining column of the new data?

Because currently this code keeps on running for each change in any cell which makes the code to run every time on any change in any cell.

We wan restrict the code to run on specific column cell change.

[Norlina Deli]

its at column A @ Sixthsense

[Norlina Deli]

the rest of the column is just for the formula to calculate the duplicate in a week and restrt the duplicate count every week

[:) Sixthsense :)]

Replace your current code with the below one

Please Login or Register  to view this content.

[Norlina Deli]

hi @ Sixthsense , not sure if I did something wrong again but its not auto calculating the data.

[:) Sixthsense :)]

hi @ Sixthsense , not sure if I did something wrong again but its not auto calculating the data.

Try this...

Please Login or Register  to view this content.

[Norlina Deli]

hi @ Sixthsense , I feel really bad now , coz nothing is happening now as well . I'm so sorry for bothering you again . You have no idea how much I appreciate this.

[Norlina Deli]

hi @ Sixthsense , the issue is solved now. Thank you so much for helping me again