Function in a loop

[bastanu]

That is what I had in post 10 as the first alternative, but that is not the problem.
A VBA function returns a value; in your you do not as you are using a variable declared in an external sub to assign the value to. You need to replace "startRange" with "BeginDate" everywhere within the function code (and a similar change for the other).

 

[bastanu]

Private Function BeginDate(rsLeave As Date, rownum) As String

    
If Month(rsLeave) = "1" Then
    If Day(rsLeave) = "1" Then
        BeginDate = "CR" & rownum

 

[Valentine]

YES!!!!! awesome thank you soo much it works just like I want it to.

 

[bastanu]

You're very welcome, good luck with your project!

 

[Gasman]

Instead of writing a humingous piece of code hardcoded, you can refer to a range by column and row numbers. So should be easy to calculate in a few lines?

ADDRESS Function

The Excel ADDRESS function returns the address for a cell based on a given row and column number. For example, =ADDRESS(1,1) returns $A$1. ADDRESS can return an address in relative, mixed, or absolute format, and can be used to construct a cell reference inside a formula.

exceljet.net

 

[Valentine]

Yeah but the functions were giving me the specific cell based on 3 different unknowns. The spreadsheet was a home made colander of sorts, row 1 had the months by name "January", "February", and so on as merged cells since row 2 had the number day of the month "1", "2", and so on. So I had to go through the record set of dates and pull out the month of each day to get the 1st range and then the day of the date to get the actual column title "A", "B", and so on. I had the rownum variable already pull the row value so I just combined the function with the rownum variable to give the specific cell each date refered to. I was pulling the dates in from a recordset from access.