Error 1004 when I use a Range.Find

zelarra

New Member
Joined
Jan 2, 2021
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
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:

VBA Code:
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.


Thank you!
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,400
VBA Code:
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...
VBA Code:
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
 
Last edited:

zelarra

New Member
Joined
Jan 2, 2021
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
Hi, I just tried it and it keeps giving me the error. Tell me if you need to know anything else.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,686
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jan 2, 2021
Messages
6
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Ok, I'm sorry
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,400
I'm not sure why your function errors, but you could do what the function does with a simple lookup formula

Excel 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

New Member
Joined
Jan 2, 2021
Messages
6
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Jan 2, 2021
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
I just disabled the volatile functions and it no longer gives me the 1004 error
 

zelarra

New Member
Joined
Jan 2, 2021
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
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.
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,127,911
Messages
5,627,594
Members
416,255
Latest member
amethystia

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
Top