Can I store a dynamic cell reference within a variable?

Viewed 40

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
0 Answers
Related