Results 1 to 5 of 5

For Loop not completing

Threaded View

  1. #1
    Registered User
    Join Date
    09-01-2011
    Location
    Delhi, India
    MS-Off Ver
    Excel 2010
    Posts
    3

    For Loop not completing

    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 last col 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
    Last edited by somnath.deb; 09-01-2011 at 11:52 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1