Error 1004 when I use a Range.Find

Good afternoon and happy new year.

I am new here. Let's see if you can help me with a problem I have.

I have developed a function where I use find to find the cell that corresponds to today's date. However, it gives me an error 1004 that I don't know why it happens. What I have done has been to add an error control to skip it, but I want to know the reason for the error, to know if it can be solved before killing flies with gunfire.

This is the function:

Please Login or Register  to view this content.

I also enclose the book so that you can see the best procedure. You just have to click on the button on the right above and it will give you the error.

Thank you!

Originally Posted by sintek

Not sure what you are trying to do but this will give you an idea...

Please Login or Register  to view this content.

Hello, thanks for answering.

I have tried what you propose and it still does not work without errors.

This sheet is a summary of hours by tasks, and to avoid having to stop each month to add a new month, copying a row, looking for which cells I have to expand the range ... what I do is automate it.

Also, and this is where I think the problem is, is that I have created custom formulas to calculate the data that I need to have, hence why you see so many functions in the VBA project. You can see it in the purple cells.

Let's see, the problem is solved as I indicate, adding an error control, capturing the 1004, and doing nothing. But I want to know why it gives this error, in case it could affect something else that I have on the sheet.

Well then, thank you very much for the time. I have tried to simplify, so that instead of leaving the MsgBox that you have put me six times, it only comes out once, let's see if that's the case, but neither.

Let's see if someone else comments something different.

This function is to find the hours of the current month. That is, we are at January-21 (there is still no data for hours, that is, it is empty or null), when it looks for it it tells me that it is in row 19, and then with a Cells, I take that row and put the column two and so I get the hours. And with those hours what I do is obtain various ratios of my interest.

Originally Posted by sintek

what must the value of HorasDedicadasMesActual be...

the value in column b corresponding to the date of the current month

for example, for last month it was 87:05

yes, that's right, you got it

Ok, I'm sorry. I won't do in the future

Originally Posted by sintek

So what is wrong with post 6 code...Does all you require...
How are the other columns in row 19 populated...

If it is not bad, it is perfect, the issue is that when copying row 12 below the last filled row, with the button you have on the top right, I get error 1004. The function is AñadirNuevoMesFocusToDo

Originally Posted by sintek

If I were you...I would get rid of all that functions and tell us exactly what it is you are trying to achieve...
I suspect that one sub will provide everything you need...if only we knew what you are trying to achieve...

Looks like you are just trying to increment date lines and populate the columns B to S with values and formulas...

I have translated the sheet for you, except for the dates, which I could not, and I have added comments with what each thing means. Let's see if it is clear to you what I want to achieve.

Yes I implemented it, what happens to you is that as I have been testing, I will have sent you the Excel with the comments in the original, not the one in which I have tested what you were telling me.

Sure enough, I only push that button once a month. Then all the information is on this sheet.

Other things I have tried: if I disable all volatile functionals, it does not give error 1004 when pressing the button.

Thank you

Originally Posted by sintek

I have provided advice...I am only able to offer a simplistic solution by not using all your functions if you are able to ...


If not...I apologize for wasting your time...Perhaps someone else is willing to hop on board and offer a fix to your function problem as my code has not solved this...

How are you going to waste my time, at all. I greatly appreciate your interest. Thanks a lot.

Goodnight. I put what I have done to solve it.

Basically, it has been removing Application.Volatile from all functions. That has removed the 1004 error from automating adding a new month using a button. Now, I had to fix the update of the functions. Apparently UDFs update when their arguments change, so I've made sure their arguments actually change. To do this, I had to change a single formula in which I had a UDF without arguments and reference it to others that did change when updating the sheet.

Finally, and this is not the subject of this question, I have added a custom format: [h]: mm; [Red] - [h]: mm; [Black] [h]: mm.

Thank you very much for your help!

PS: I'm so sorry I did cross-posting. It will not happen again.

Thank you for your help.

That would be another solution. Thanks