Is there a way to store a specific cell reference with a dynamic variable?
Basically I had to reference output cells as ws.cells(dayOutNR,x) but it becomes hard to read when I have to repeat the code every time I need to output data. I wanted to store it as dayOutput.
Thanks in advance :)
Public Function annDays(ByVal cfDate As Date, ByVal expDate As Date, ByVal currentPeriod As Date, ByVal outputNR As Long, ByVal inputNR As Long) As Double
Dim ws As Worksheet
Set ws = Sheets("SUMMARY")
Dim dayOutNR As Long, dayInNR As Long
dayOutNR = outputNR
dayInNR = inputNR
'restrict incorrect logic to reduce workings
If expDate <= cfDate Or _
cfDate = 0 Or _
expDate = 0 Then
Exit Function
End If
**'create and store range for ref. Use x for column count throughout function
Dim dayOutput As Range, x As Long: x=25
set dayOutput = Cells(dayOutNR, x)**
'Calculate total days in range, accounting for LeapYear
Dim y As Long, leapYr As Date
For y = Year(cfDate) To Year(expDate)
leapYr = DateSerial(y, 2, 29)
If Day(leapYr) = 29 And leapYr >= cfDate And leapYr <= expDate Then
dayOutput = DateDiff("d", cfDate, expDate)
Else
dayOutput = DateDiff("d", cfDate, expDate) + 1
End If
Next y
'create variable to increment (set to April 1, xxxx by default)
Dim incMonth As Date
If Month(cfDate) = 1 Or Month(cfDate) = 2 Or Month(cfDate) = 3 Then
incMonth = DateSerial(Year(cfDate) - 1, 4, 1)
Else
incMonth = DateSerial(Year(cfDate), 4, 1)
End If
'Calculates & increments number of months before currentPeriod (x = 13 is April)
x = 13
Do While Month(incMonth) <> Month(currentPeriod)
incMonth = DateAdd("m", 1, incMonth)
x = x + 1
Loop
If currentPeriod >= expDate Then
If cfDate <= leapYr And expDate >= leapYr Then
**ws.Cells(outputNR, x)**= DateDiff("d", cfDate, expDate)
GoTo bottomFunction
Else
**ws.Cells(outputNR, x)**= DateDiff("d", cfDate, expDate) + 1
GoTo bottomFunction
End If
ElseIf cfDate <= currentPeriod Then
If cfDate <= leapYr And currentPeriod >= leapYr Then
**ws.Cells(outputNR, x)**= DateDiff("d", cfDate, DateSerial(Year(currentPeriod), Month(currentPeriod) + 1, 0))
currentPeriod = DateAdd("m", 1, currentPeriod)
x = x + 1
Else
**ws.Cells(outputNR, x)**= DateDiff("d", cfDate, DateSerial(Year(currentPeriod), Month(currentPeriod) + 1, 0)) + 1
currentPeriod = DateAdd("m", 1, currentPeriod)
x = x + 1
End If
End If