Need help Listing Dates Within Ranges

[clarka9]

Good Afternoon,

I am struggling over here.
I need help in trying to take a name (BoB)and a date range (1/1/2013-12/1/2013), and list it as the following on the next worksheet........

I dont know how to write this code in VBA, and I would sure appreciate the help. Thanks!!

BoB</SPAN>		1/1/2013</SPAN>
BoB</SPAN>		1/2/2013</SPAN>
BoB</SPAN>		1/3/2013</SPAN>
BoB</SPAN>		1/4/2013</SPAN>
BoB</SPAN>		1/5/2013</SPAN>
BoB</SPAN>		1/6/2013</SPAN>
BoB</SPAN>		1/7/2013</SPAN>		etc!!

<TBODY>
</TBODY><COLGROUP><COL><COL></COLGROUP>

 

[Arovin]

Hey clarka9,
try the code below,emember to change the variables to values you need.

Sub bob()

    Dim Name As String
    Dim FromDate As Date
    Dim ToDate As String
    Dim ws As Worksheet
    
    Name = "Bob"
    FromDate = "01/01/2013"
    ToDate = "07/01/2013"
    Set ws = ActiveSheet
    
    For i = 1 To DateDiff("d", FromDate, ToDate)
        ws.Cells(i, 1).Value = Name
        ws.Cells(i, 2).Value = FromDate + i
    Next i

End Sub

 

[clarka9]

Thanks for your help. I appreciate it!
What if I have an excel spreadsheet as below?
I am trying to automate a sheet that will be updated continously as I move on, but I will need the code to break the ranges out for each on the next sheet.
Thanks ALOT!!!!!!!......

First Name</SPAN>	Last</SPAN>	Assign Begin Dt</SPAN>	Assign Retn Dt</SPAN>
Anthony</SPAN>	Teft</SPAN>	1/7/2013</SPAN>	5/31/2013</SPAN>
John</SPAN>	Mahoney</SPAN>		12/31/2014</SPAN>
Adam</SPAN>	Sweet</SPAN>	10/13/2008</SPAN>	12/31/2013</SPAN>
Gregory</SPAN>	Klock</SPAN>	3/11/2013</SPAN>	3/20/2013</SPAN>
Kevin</SPAN>	Sauve</SPAN>	3/11/2013</SPAN>	3/24/2013</SPAN>
Paul</SPAN>	Barnes</SPAN>	12/3/2012</SPAN>	12/23/2012</SPAN>
Matthew</SPAN>	Seibert</SPAN>	3/11/2013</SPAN>	3/24/2013</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL><COL></COLGROUP>

 

[Arovin]

Hey clarka9,
please see the code below. You also have to change the name of your target sheet. This assumes that your source data starts at cell A1 in your active worksheet.

Did you intentionally leave assign begin dt for John Mahoney blank? The way the code below works it would assume that a blank date is equal to 1/1/1900, which would result in quite a workload for John...

Sub bob()

    Dim FirstName As String
    Dim lastName As String
    Dim FromDate As Date
    Dim ToDate As String
    Dim ws As Worksheet 'Your target sheet
    Dim wsData As Worksheet 'Your source data
    Dim lastRow As Long
    Dim lastNameRow As Long
    
    Set ws = Sheets("Sheet2") 
    ws.Range("A1:C1") = Array("First Name", "Last Name", "Date")
    
    Set wsData = ActiveSheet
    
    'Change the reference below to "A65536" in case you are using Excel 2003
    lastNameRow = wsData.Range("A1048576").End(xlUp).Row
    
    For i = 2 To lastNameRow
        
        FirstName = wsData.Cells(i, 1).Value
        lastName = wsData.Cells(i, 2).Value
        FromDate = wsData.Cells(i, 3).Value
        ToDate = wsData.Cells(i, 4).Value
        lastRow = ws.Range("A1048576").End(xlUp).Row
        
        For n = 1 To DateDiff("d", FromDate, ToDate)
            ws.Cells(n + lastRow, 1).Value = FirstName
            ws.Cells(n + lastRow, 2).Value = lastName
            ws.Cells(n + lastRow, 3).Value = FromDate + n
        Next n

    Next i

End Sub

 

[clarka9]

ADVERTISEMENT

The blank is suppose to be there. I guess that information wasnt filled in.
I will find it.
But what is this "A65536"?
Is this the last cell in Excel?

 

[Arovin]

A65536 is the last row in column A - if you are using Excel 2003. In version 2007/2010 this limit was raised to 1048576.
range.end returns the end of a region in a provided range:

lastNameRow = wsData.Range("A1048576").End(xlUp).Row

[/CODE]

In this case the application goes up from the last row in column A until it finds a cell containing a value.

 

[clarka9]

I am recieving an error msg in regards to that code.
"Method Range of Work object failed"