Not Responding Error in VBA Loop

[zaska]

Hello,

The following code is generating output but the Code is not ending and generating not responding error at last and also the first opened file is not closing.

When i tried checking the code with F8 everything went fine.


I am unable to locate the error in this code. Kindly help me in this regard

Sub test()   Dim strPath As String, startPath As String, Endpath As String, i As Long, buffer As String, startDate As Date, LR As Long           startPath = "E:\Macros\Input\" 'current directory     Endpath = "E:\Macros\Output\"   'output path     startDate = "01-JUN-2011" 'DDMMMYYYY          Do While buffer <> "02-JUL-2011"         buffer = Format$(startDate, "DDMMMYYYY")         strPath = startPath & "fo" & buffer & "bhav.csv"   'full path of the file you want to open         otrpath = Endpath & "fo" & buffer & "bhav.txt"         startDate = startDate + 1 'add one day           If Len(Dir(strPath)) > 0 Then Workbooks.Open Filename:=(strPath) Application.ScreenUpdating = False LastRow = Cells(Rows.Count, "B").End(xlUp).Row  With Range("A1:O" & LastRow)      .AutoFilter Field:=1, Criteria1:="<>FUTIDX", Operator:=xlAnd, Criteria2:="<>FUTSTK"      .Offset(1, 0).EntireRow.Delete  End With  ActiveSheet.AutoFilterMode = False  LR = Range("A" & Rows.Count).End(xlUp).Row     Columns("C").Insert     Range("C2").Value = "-I" For i = 3 To LR     If Range("B" & i).Value = Range("B" & i - 1).Value Then         Range("C" & i).Value = Range("C" & i - 1).Value & "I"     Else         Range("C" & i).Value = "-I"     End If Next i For i = 2 To LR     Range("B" & i).Value = Range("B" & i).Value & Range("C" & i).Value     Range("D" & i).NumberFormat = "yyyymmdd"     Range("D" & i).Value = Range("P" & i).Value Next i Columns("C").Delete Columns("N:O").Delete Columns("L").Delete Columns("J").Delete Columns("D:E").Delete Columns("A").Delete Range("B1").Value = "TIMESTAMP" Range("I2:I" & LR).Formula = "=G2*LOOKUP(9.99999999999999E+307,SEARCH(""#""&'[NSE Converter.xls]Sheet1'!$A$1:$A$226,""#""&SUBSTITUTE(TRIM(A2),CHAR(160),"""")),'[NSE Converter.xls]Sheet1'!$B$1:$B$226)" For i = 2 To LR Range("G" & i).Value = Range("I" & i).Value Next i Columns("I").Delete Rows("1").Delete Application.ScreenUpdating = False Application.DisplayAlerts = False ActiveWorkbook.SaveAs Filename:=(otrpath), CreateBackup:=False ActiveWorkbook.Close Application.DisplayAlerts = True End If Loop   End Sub

Regards,

Zaska
</pre>

 

[AlphaFrog]

The format you are using to set Buffer "DDMMMYYYY" is not the same as the Text string "DD-MMM-YYYY" you are comparing it to.

This just compares date values irregardless of date format.

    Do While [COLOR="Red"]startDate < DateValue([/COLOR]"02-JUL-2011"[COLOR="Red"])[/COLOR]
        buffer = Format$(startDate, "DDMMMYYYY")
        strPath = startPath & "fo" & buffer & "bhav.csv"   'full path of the file you want to open
        otrpath = Endpath & "fo" & buffer & "bhav.txt"
        startDate = startDate + 1    'add one day

 

[zaska]

Sir,

Thank you now the problem got solved.