For Loop not completing

[somnath.deb]

Hi All,
I am relatively new to macro and I was trying to use For loop in my code. It appears the loop is not running till end. My excel has some date columns but there are few columns where there is a gap in date between two consecutive columns. So what I was trying to do is to insert a column whereever there is a gap. and fill out the column header with the appropiate coulmn value. Below is the code that I wrote:

For i = 6 To lastcol
    colname1 = ColLetter(i)
    colname2 = ColLetter(i - 1)
    If (DateDiff("d", CDate(Range(colname2 & CStr(2)).Value), CDate(Range(colname1 & CStr(2)).Value)) > 1) Then
        For j = 1 To DateDiff("d", CDate(Range(colname2 & CStr(2)).Value), CDate(Range(colname1 & CStr(2)).Value)) - 1
            Columns(colname1).Select
            Selection.Insert Shift:=xlToRight
            nxtdt = DateAdd("d", 1, CDate(Range(colname2 & CStr(2)).Value))
            Range(colname1 & CStr(2)).Value = nxtdt
            Range(colname1 & CStr(2)).Select
            curcol = ActiveCell.Column
            colname1 = ColLetter(curcol + 1)
            colname2 = ColLetter(curcol)
            lastcol = lastcol + 1
            i = i + 1
        Next j
    End If
Next i
MsgBox i & " , " & j & " , " & lastcol

on executing the code the value that I am getting for i is 40 and of lastcol as 52 (lastcol is the last column of the datarange).
Will deeply apreciate if anyone can guide me on the mistake that I am making.
Thanks
-Som

 

[Andrew Poulsom]

Welcome to MrExcel.

If you are inserting columns you need to loop backwards, otherwise you will never reach the last column because it's greater than it was at the start.

 

[somnath.deb]

There is a small miss I should have mentioned. Column2 is my first colun and column1 is the next one.. I am not a programmer so it may look little odd.. however the columns are getting populated and date are getting inserted properly. The only hitch is that the loop is not completing till the lastcol

 

[somnath.deb]

There is a small miss I should have mentioned. Column2 is my first colun and column1 is the next one.. I am not a programmer so it may look little odd.. however the columns are getting populated and date are getting inserted properly. The only hitch is that the loop is not completing till the lastcol

Btw thank you for accepting my membership and the quick review

 

[Andrew Poulsom]

Did you try looping backwards?

 

[somnath.deb]

Did you try looping backwards?

didn't try that as the columns are correctly getting inserted and date getting populated. I will try to do the reverse way and will keep you posted. Thanks for the suggestion.