Create a new function to find a date or next date on a selected range

[Ifret]

Hello my friends,

I'm new here and hope you guys can gimme a little help!
I checked the posts but didn't find anything that could match my question but if by any chance I missed something please forgive me.

Basically as I mentioned in the title what I need is to create a new function on VBA for the Excel that will work in a very similar way to the PROCV function. The difference is that mine is more specific, works only for dates, don't look for others information besides the date itself and when I get no match on the search the function will show me the next date.

Looking For Next Date Function.xlsm

I tried to attach my excel file here, hope this works.

Well, the point is that the function works, but for some reason it only recognizes the range (I called Matriz) when it's a direct input on the cell, if I link one cell to another the function don't work...

If anyone has any clue of how I could solve this issue please let me know, would really help a lot!
Last sorry about my bad english!


Thank you very much in advance!

[Ifret]

Hello again!

I just sent the first and here I'm again asking more... sorry about that!

The reason I'm back so fast is that I forgot to ask one question.
I couldn't find a way to solve the problem that my new function can't work with linked data (Like date is on cell "D2" and my cell "A2" is like =D2 and my function is searching a range from "A2" to "A7" for exemplo) so I tried to change the information format in many ways but without success.
Last I create a macro to copy the dates form wherever they are and Paste Special as value on the place I'm using the new function.
The idea works... the function works with the date I copyed... the problem is that if I run the macro to make the copy for some reason my new function goes on a looping...
I can cancel the looping by pressing Esc and then just update the cell with the new function and it works but if anyone has any idea of how could I make this procedure workes I would really appreciate it!! In the end I'll need to use this new function in a range of variable data that will be updated daily from an external link from another file, so I really need someway to make it works like this.

Proc to Date or Next Date.xlsm

I made a few changes on the last file to try to make it easier to understand what I need, hope someone can give me a little help with this!

Once again thank you all in advance for the attention and help!
Best regards!

[SamT]

This is based on your first ersion

I can not find any logical problems with it, but, I can't test it because my computer is telling me that the array index is out of bounds, which is programmatically impossible in this code.

Option Explicit

Public Function PROCP(Procurado As Range, Matriz As Range) As Variant
'For help see: http://www.vbaexpress.com/forum/showthread.php?62086
Dim PDate As Date
Dim Matrix As Variant 'To make into an Array
Dim i As Long 'For use with Array

  PDate = Procurado 'To avoid using input Parameters
  Matrix = Intersect(Matriz.Parent.UsedRange, Matriz.EntireColumn).Value 'Use an Array
  
  For i = LBound(Matrix) To UBound(Matrix)
    If PDate = Matrix(i) Then
      PROCP = Matrix(i)
      Exit Function
    End If

    PDate = DateAdd("d", 1, PDate)
  Next i

End Function

[Ifret]

Hello, my friend SamT!

First sorry to take so long to answer it seems that I have some limitations on posting here.
I would like to say that I really appreciante your attention and help, thank you!

Now for the feedback:
I tried your code directly and unfortunately it didn't work in any situation. I think I could understand the logic of the code and in my view it has nothing wrong but it's still not working... It always show the #VALUE! error.

Option Explicit

Public Function PROCP(Procurado As Range, Matriz As Range) As Variant

Dim PDate As Date
Dim Matrix As Variant 'To make into an Array
Dim i As Long 'For use with Array

MsgBox (Procurado)
  PDate = Procurado 'To avoid using input Parameters
MsgBox (PDate + 1)
  Matrix = Intersect(Matriz.Parent.UsedRange, Matriz.EntireColumn).Value 'Use an Array
MsgBox (PDate + 2)
  For i = LBound(Matrix) To UBound(Matrix)
    If PDate = Matrix(i) Then
      PROCP = Matrix(i)
      Exit Function
    End If

    PDate = DateAdd("d", 1, PDate)
  Next i

End Function

I tried to include a MsgBox() in the beginning to see if the function was running normally and it seens to be but I don't know very well how the For function works and also don't know well how the commands LBound and UBound works.

Well, any new ideas please let me know!!

Thank you very much!
Best regards.

[p45cal]

In answer to your msg#1 and in the function in that attachment:
Everywhere you have:
.Find(Procurado, , , xlWhole)
change it to:
.Find(Procurado, , xlValues, xlWhole)

[p45cal]

I can't test it because my computer is telling me that the array index is out of bounds, which is programmatically impossible in this code.

SamT, I suspect because you created the array from a range it has two dimensions (even if it's only a single column) but you only ever try to refer to it in the code with a single dimension.

[Ifret]

In answer to your msg#1 and in the function in that attachment:
Everywhere you have:
.Find(Procurado, , , xlWhole)
change it to:
.Find(Procurado, , xlValues, xlWhole)

p45cal, you are really a life saver!

Thank you very much! It worked perfectly after including "xlValues"!

I'm acctually new in VBA programming so if you don't mind to explain what does the xlValues parameters do in this function? Does it make the function to recognize all informations as value?

Well, for all who checked my post and tried to help thank you for the attention, my second question was an alternative if there were no solution for the first one. Thanks to p45cal that one was solved so I'll be closing the Thread as solved now!

Once again thank you all for the attention and help,
Best regards!

[p45cal]

Ifret, you will also see your original file attached working properly without changes, if just once you use the Find dialogue (Ctrl+f on the keyboard) with the Values setting:
Capture3.jpg
The range.find method has these comments in Excel's Help:
The settings for LookIn, LookAt, SearchOrder, and MatchByte are saved each time you use this method. If you do not specify values for these arguments the next time you call the method, the saved values are used. Setting these arguments changes the settings in the Find dialog box, and changing the settings in the Find dialog box changes the saved values that are used if you omit the arguments. To avoid problems, set these arguments explicitly each time you use this method.
You can add SearchFormat to that too.

Also, be aware that when using xlValues in this method, it will not search values in hidden rows/columns (eg. in filtered ranges), whereas xlFormulas will search hidden rows/columns but will only look at the formulae (the formula is the same as the value if there is no formula).

[Ifret]

p45cal, thank you very much! That really helps a lot!

Best Regards!