Date wise code

[ashish mehra]

Hi,

Please check attached file, output is mentioned.

Regards,
AM

 

[vletm]

There is no file ...

 

[ashish mehra]

There is no file ...

File attached.

 

[Deepak]

You have missed a interval in output

KCRJ151714 25-Mar-16

Check this..

Sub test()
Dim i As Integer, r As Range, x As Integer, d As Integer

i = 4
For Each r In [b4:b7]
    d = 0: x = 0
        Do While x < r.Offset(, 2) - r.Offset(, 1) + 1
            Cells(i, 10) = r
            Cells(i, 11) = r.Offset(, 1) + d
            d = d + 1: i = i + 1: x = x + 1
        Loop
Next

End Sub

 

[ashish mehra]

You have missed a interval in output

KCRJ151714 25-Mar-16

Check this..

Sub test()
Dim i As Integer, r As Range, x As Integer, d As Integer

i = 4
For Each r In [b4:b7]
    d = 0: x = 0
        Do While x < r.Offset(, 2) - r.Offset(, 1) + 1
            Cells(i, 10) = r
            Cells(i, 11) = r.Offset(, 1) + d
            d = d + 1: i = i + 1: x = x + 1
        Loop
Next

End Sub

Yes I missed an interval.

Thanks for VBA solution, can this be achieved with formula.

Regards

 

[NARAYANK991]

Hi Ashish .

See if this is OK.

Narayan

 

[ashish mehra]

Hi Ashish .

See if this is OK.

Narayan

Hi Narayan,

Could not able to download Excel file. Pl paste formula.

Regards

 

[NARAYANK991]

Hi Ashish ,

1. I have used a helper column , column E.

In E4 , enter the formula =D4-C4+1

Copy down.

2. I have put in the outputs in columns J and K. The formulae are different for the first row and thereafter.

In J4 , enter the formula =B4

In K4 , enter the formula =C4

3. In the second row , enter the following formulae :

J5

=IFERROR(IF(INDEX($E$4:$E$7,MATCH(J4,$B$4:$B$7,0))>COUNTIF(J$4:J4,J4),J4,INDEX($B$4:$B$7,MATCH(J4,$B$4:$B$7,0)+1)),"")

K5

=IF(J5="","",IF(J5<>J4,INDEX($C$4:$C$7,MATCH(J5,$B$4:$B$7,0)),K4+1))

Copy down as far as required.

Narayan

 

[ashish mehra]

Thanks Deepak for code.

Thanks Narayan for explaining formulas in precise way.

Regards
AM