I am new to VBA in Excel. Mostly I've just been dabbling in simple user-defined functions, and have been pretty successful up to this point. Now, I'm trying to set up a function that will return the number of months between two dates. One date is determined by a previous UDF, but the other is called out in column D of each row. I have named the range "POP_End", and that works as a relative reference when I use the formula directly in the spreadsheet. However, if I use it as a reference in my VB module, it seems to include the entire range, rather than the one cell that is relative to the cell where I enter the function. Here's my code:
Function nummonths()
Application.Volatile True
If Day(Range("POP_End").Value) >= 15 Then
nummonths = (Year(Range("POP_End").Value) - Year(prevpayperiod)) * 12 + Month(Range("POP_End").Value) - Month(prevpayperiod)
Else
nummonths = (Year(Range("POP_End").Value) - Year(prevpayperiod)) * 12 + Month(Range("POP_End").Value) - Month(prevpayperiod) - 1
End If
End Function
Basically, I need it to select the value in the cell in range "POP_End" that is in the same row as the active cell.
Bookmarks