Counting days issue

Hi Folks, new to this forum as I am looking for a solution that has got me stuck.

The attached sheet shows a number of calendar dates in row A, In column A2:Ax are the number of days that a training course will last, I have a macro that enters the start date of each course, but cannot find a way to add the next day(s) based on the value in Column A.
The final sheet should look like part two of my attachment.

I need a macro for this as I have around 200 values in Column A and around 6months worth of training dates in Row A.

Any assistance will be greatly appreciated.

IanE

Maybe:

Sub IanE()
Dim i As Long
Dim y As Long
Dim x As Long
For i = 2 To Range("A" & Rows.Count).End(3).Row
y = Cells(i, 1).Value - 1
    For x = 9 To 3 Step -1
        If Cells(i, x).Value <> "" And y >= 1 Then
            Range(Cells(1, x + 1), Cells(1, x + y)).Copy Cells(i, x + 1)
        End If
    Next x
Next i
            
End Sub

Hi John,

Many thanks for the swift reply, and a working solution. Only issue I have is it reporting a "Run Time error 13" Type mis-match after it has completed.
Any idea?

Can you provide a sample workbook (one which produces the error), with some dummy data?

Hi John, Error has gone, once I saved the work book and then re-run the macro it had gone. Thanks
However I see your solution is only working for the 9 columns as per the sample, I need this to run for "Xn" number of columns,
is it a case of running a statement to count left on the sheet?

Try:

Sub IanE()
Dim i As Long
Dim y As Long
Dim x As Long
For i = 2 To Range("A" & Rows.Count).End(3).Row
y = Cells(i, 1).Value - 1
    For x = Cells(i, Columns.Count).End(xlToRight).Column To 3 Step -1
        If Cells(i, x).Value <> "" And y >= 1 Then
            Range(Cells(1, x + 1), Cells(1, x + y)).Copy Cells(i, x + 1)
        End If
    Next x
Next i
            
End Sub

Hi John, Many thanks for the swift response and solution. Working a treat across my entire sheet.

You're welcome. Glad to help out and thanks for the feedback. Please comply with the Forum Rules and mark this thread as solved.

http://www.excelforum.com/forum-rule...rum-rules.html