Comparing Dates With User-Defined Function

[eschatite]

I have (attempted) to create a function that will take a date as an argument, search for the number of occurrences of that date in a different sheet, increment a counter for each occurrence, and then return the counter. I keep getting 0 as the returned value.

Function code:

Please Login or Register  to view this content.

day is assigned the cell that contains the date being searched for
area is the name of the sheet I'm searching in
area_range is the number of active rows in the sheet I'm searching in

Example function call:

=Misloads.xlsm!get_daily_misloads.get_daily_misloads(A2,"Day_Sort",day_sort_range)

Active sheet:
Active_Sheet.PNG

Target Sheet:
target_sheet.PNG

I've been wrestling with this for FAR too long so any help would be most appreciated.

Thanks

[teylyn]

What happens if you step through the code and inspect the values in the IF statement condition? Are they the same? If not, how do they differ? Basic troubleshooting.

[eschatite]

What happens if you step through the code and inspect the values in the IF statement condition? Are they the same? If not, how do they differ? Basic troubleshooting.

I'm not familiar with how to do that for a function.

[dflak]

Make a test subroutine like

Please Login or Register  to view this content.

and then put a break point on the msgbox.

[MrShorty]

In this tutorial, I talk a little about debugging UDF's: https://www.excelforum.com/tips-and-...uild-udfs.html My approach is to add a Stop statement to the top of the UDF so that the code will stop and enter debug mode once I enter the function in the spreadsheet or trigger the UDF to calculate (by editing an input value, for example). It may take a few tries to find the best places for Stop statements so that I can quickly get to the real debugging, and they may need to move around as the process evolves. The key thing to learn is that you need to use Stop statements (or breakpoints) with UDFs in order to enter debug mode and debug what is happening.

[eschatite]

Ok. So, when debugging, I got this:

error.PNG

[eschatite]

In this tutorial, I talk a little about debugging UDF's: https://www.excelforum.com/tips-and-...uild-udfs.html My approach is to add a Stop statement to the top of the UDF so that the code will stop and enter debug mode once I enter the function in the spreadsheet or trigger the UDF to calculate (by editing an input value, for example). It may take a few tries to find the best places for Stop statements so that I can quickly get to the real debugging, and they may need to move around as the process evolves. The key thing to learn is that you need to use Stop statements (or breakpoints) with UDFs in order to enter debug mode and debug what is happening.

I will check that out. Thanks

[MrShorty]

From that watch list, what is in area? What is in i? Where was this at in the code (possibly before i had been assigned its first value, so that cells(nothing,9) has no meaning)?

[eschatite]

I'm an idiot. I had the year set to 2019 in active sheet instead of 2018

[dflak]

There's a lot of that going around this time of year