Setting a dynamic starting point for output

[l2evenge]

Hello MrExcel Gurus,

I have a question that I bet has a simple answer, I just haven't run across it yet. I have code that I created in order to take two dates and list in order ever date in between the two in column A. This is working as expected, but now I need to change the startCell variable to be dynamic. I am doing this because I will be referencing this block of code in a loop and need to continue adding dates to the same column x number of times.

Currently my code is:

Option Explicit
Sub ListDate()
Dim startDate As Date 'startnumber
Dim endDate As Date 'endnumber
Dim j As Date 'loop manipulation
Dim i As Date 'loop manipulation
Dim startCell As Range 'start cell


startDate = Range("B1")
endDate = Range("B2")
Set startCell = Range("A4")


If startDate >= endDate Then 'exits macro if start is larger than end
    Exit Sub
    End If
    
 j = 0
 For i = startDate To endDate
    startCell.Offset(j, 0) = i
    j = j + 1
 Next i


End Sub

startCell will never be any higher in the column than "A4".

Please give me your suggestions. Thanks!

 

[l2evenge]

So I've figured out how to do what I want it to do! Please take a look and see if there might be a more efficient way to achieve these same results please.

So I created some code to pass the location of the first empty cell in the column to rng2:

Option Explicit


Public rng2 As Range


Sub startCell()


'Dynamically passes last empty cell to rng2 variable.


'Sheets("sheet1").Select
Dim LR2 As Long, cell2 As Range
With Sheets("sheet1")
    LR2 = .Range("A" & Rows.Count).End(xlUp).Row
    For Each cell2 In .Range("A4:A" & LR2)


    Next cell2


End With


'Range("D1") = LR2 + 1
    Set rng2 = Range("A" & (LR2 + 1))
'rng2.Select
End Sub

Then I run my code to list dates with the variable rng2 as startCell variable:

Option Explicit
Sub ListDate()
Dim startDate As Date 'startnumber
Dim endDate As Date 'endnumber
Dim j As Date 'loop manipulation
Dim i As Date 'loop manipulation
Dim startCell As Range 'start cell


startDate = Range("B1")
endDate = Range("B2")
Set startCell = rng2


If startDate >= endDate Then 'exits macro if start is larger than end
    Exit Sub
    End If
    
 j = 0
 For i = startDate To endDate
    startCell.Offset(j, 0) = i
    j = j + 1
 Next i


End Sub

So now for each item in my list I will run startCell macro followed by listDate macro. Now I need to figure out how to copy the first item into an adjacent column for the first set of dates, second item into the second set of dates, etc. I may add another thread regarding that, but if you have any thoughts please share.

Thanks!