Error 1004 when I use a Range.Find

[zelarra]

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.

This is the function:

Function HorasDedicadasMesActual() As Date
    Application.Volatile
    On Error GoTo err_lbl
    Dim Valor As Date
    Valor = Cells(Range("A:A").Find(Format(Date, "mmmm-yy"), LookIn:=xlValues).Row, 2)
    HorasDedicadasMesActual = Valor
err_Salida:
    Exit Function
err_lbl:
    Select Case Err.Number
        Case 13
            HorasDedicadasMesActual = 0
        Case 91
            HorasDedicadasMesActual = 1
        'Case 1004 If I disable this, I get an error
        Case Else
            MsgBox Err.Number & " " & Err.Description & ActiveCell.Address
    End Select
End Function

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.

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.

Dropbox - File Deleted - Simplify your life

www.dropbox.com

Thank you!

 

[AlphaFrog]

Valor = Cells(Range("A:A").Find(Format(Date, "mmmm-yy"), LookIn:=xlValues).Row, 2)

If there is no date match for Today, you can't get the row number and thus the time value from column B.

You could test if it found a date match like this...

Dim rngValor As Range
Set rngValor = Range("A:A").Find(Format(Date, "mmmm-yy"), LookIn:=xlValues)
If Not rngValor Is Nothing Then
    Valor = Cells(rngValor.Row, 2)
Else
   MsgBox Format(Date, "mmmm-yy"), , "No Match Found"
End If

 

[zelarra]

Hi, I just tried it and it keeps giving me the error. Tell me if you need to know anything else.

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Error 1004 when I use a Range.Find
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[zelarra]

Ok, I'm sorry

 

[AlphaFrog]

I'm not sure why your function errors, but you could do what the function does with a simple lookup formula

=IFERROR(VLOOKUP(TODAY()-DAY(TODAY()-1),A:B,2,0),0)

NOTE: Volatile functions are inefficient as they recalculate every time you make a change to the worksheet. Your code makes several changes to sheet values and all your volatile functions recalculate for every change the code makes. It's better to avoid volatile functions when you don't need them.

 

[zelarra]

NOTE: Volatile functions are inefficient as they recalculate every time you make a change to the worksheet. Your code makes several changes to sheet values and all your volatile functions recalculate for every change the code makes. It's better to avoid volatile functions when you don't need them.

And how can I update the values when I change a cell?

 

[zelarra]

I just disabled the volatile functions and it no longer gives me the 1004 error

 

[zelarra]

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.