I know this question seem complicated but what I want to do is simple, I got 2 columns:
What I want to do is get the number of days per period (EndDate - StartDate + 1), add this many rows and change value of G to be written day per day.
I already coded the part below, but it doesn't seem to be right:
Sub Dates()
Dim LastRow As Long
Dim addrows
Dim FindDates
Dim CountDays
Dim dddays
Dim ws As Worksheet
Dim ws2 As Worksheet
Dim i As Long
Dim ir As Long
Set ws = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")
With Sheets("Sheet1")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row - 1
End With
addrows = 2
For ir = 1 To LastRow
FindDates = ws.Range("I" & addrows).Value
CountDays = ws.Range("L" & addrows).Value - ws.Range("I" & addrows).Value + 1
Adddays = 0
For i = 1 To CountDays
ws2.Rows("2:2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
ws2.Range("A2").Value = Sheets("Sheet1").Range("A" & addrows).Value
ws2.Range("C2").Value = FindDates + Adddays
ws2.Range("C2").Value = ws.Range("G" & addrows).Value
Adddays = Adddays + 1
Next i
addrows = addrows + 1
Next ir
End Sub
Can you help me a bit? "ws2.Range("C2").Value = FindDates + Adddays" is giving me an error 13
- I is my Starting Date
- L is my ending date
- G is where all the dates are supposed to be
What I want to do is get the number of days per period (EndDate - StartDate + 1), add this many rows and change value of G to be written day per day.
for what above this should help you:
Sub Dates()
Dim ir As Long, countDays As Long
With Sheets("Sheet1")
For ir = .Cells(.Rows.Count, "I").End(xlUp).row To 2 Step -1
With .Rows(ir)
countDays = .range("L1") - .range("I1") + 1
If countDays > 1 Then
.Offset(1).Resize(countDays - 1).Insert xlDown
.Offset(1).Resize(countDays - 1).value = .value
With .Resize(countDays).Columns("G")
.FormulaR1C1 = "=RC9+ROW()-" & .Rows(1).row
.value = .value
End With
End If
End With
Next
End With
End Sub