macro works 1 loop at a time, but stalls if run start-to-finish

Hi.

I've got this complex macro that WAS working perfectly but now stalls each time I run it. The annoying thing is that the code DOES work: if I insert a breakpoint over the "Next" statement (or even at the "Save") and only let it run one full loop at a time, then it works just fine and will run to completion (so long as I'm patient enough to loop it through by hand over & over). But since I need this thing to loop 73 times each time I run it (and each of those loops involves doing thousands of smaller loops), I'd rather find out what's going wrong & fix it, instead of spending my whole summer looping it all by hand..
So here's the code (including all the lead-in bits so you'll have as much info as possible):
Option Explicit
Option Base 1
Public SourceFile As Variant, SummaryFile As Workbook, Response, ResponseBool As Boolean, Row As Long
Public Sub SummariseBlossom()
Response = MsgBox("Have you remembered to close all open workbooks and programs (except for Excel)??", vbYesNo, "Reminder") 'otherwise it's slow
If Response = vbNo Then
Exit Sub
Else
ResponseBool = True
Call ImportAndReformat
Call SummarisingResults
Set SummaryFile = Nothing
End If
MsgBox "All Done!"
End Sub

Private Sub ImportAndReformat()
Dim ResultStr As String, sFileName As String, FileNum As Integer, Counter As Double, FileNameArr As Variant
Dim ResultsWB As Workbook, Response As Integer, lngCount As Long, NextResponse, shtCount As Double
Dim Rng As Range, WS As Worksheet, c As Range, SummaryWB As Workbook, ssCount As Double
Dim FirstAddress As String, MyArr As Variant, Rcount As Long, i As Long, AllCaps As String
Dim l As Long, lStart As String, Skip As Long, pos As Long, f As Long
Dim SUBFileNum As Integer, SourceStr As String, SUBSource As String, SUBFile As Variant, SUBHyp As String, Hyp As String
Application.DisplayAlerts = False
Application.ScreenUpdating = False
If ResponseBool = False Then
Response = MsgBox("Have you remembered to close all open workbooks and programs (except for Excel)??", vbYesNo, "Reminder")
If Not Response = vbYes Then
Exit Sub
End If
End If
ChDir "C:\Results"
With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = True
.Filters.Add "Blossom Output", "*.txt", 1
.Filters.Add "All Files", "*.*"
.Title = "Choose which T-Statistic Source File(s) to summarise"
.Show
End With
FileNum = FreeFile()
ssCount = 0
Set SummaryFile = Workbooks.Add(template:=xlWorksheet)
SummaryFile.Activate
SummaryFile.SaveAs Filename:=Application.GetSaveAsFilename(SourceFile, "Excel Workbooks(*.xls), *.xls", _
, "Choose a one-word name for the Summary workbook")

'***************HERE'S WHERE THE TROUBLESOME LOOP STARTS:***********
For f = 1 To Application.FileDialog(msoFileDialogFilePicker).SelectedItems.Count
SourceFile = Application.FileDialog(msoFileDialogFilePicker).SelectedItems(f)
FileNameArr = Split(CStr(SourceFile), "\", 7)
shtCount = 1
Open SourceFile For Input As #FileNum
If Not f = 1 Then
SummaryFile.Worksheets.Add After:=Worksheets(Worksheets.Count)
End If

SummaryFile.Worksheets(Worksheets.Count).Name = "T_File" & f
Counter = 1
Do While Seek(FileNum) <= LOF(FileNum)
Application.StatusBar = "Importing Row " & Counter & " of output file " & SourceFile
Line Input #FileNum, ResultStr
If Left$(UCase(ResultStr), 2) Like Left$(UCase(FileNameArr(UBound(FileNameArr) - 1)), 2) Then
Row = 1
If Not ActiveCell.Address = "$A$1" Then
ActiveCell.Offset(1 - ActiveCell.Row, 1).Select
If ActiveCell.Column = 220 Then
ActiveWorkbook.Sheets.Add After:=Worksheets(Worksheets.Count)
Worksheets(Worksheets.Count).Range(Cells(1, 1), Cells(1, 251)).Font.Bold = True
shtCount = shtCount + 1
Worksheets(Worksheets.Count).Name = "T_File" & f & "#" & shtCount
Cells(1, 1).Select
End If
End If
ssCount = ssCount + 1
ActiveCell.Value = "ss" & ssCount
Else
Row = Row + 1
ActiveCell.Value = ResultStr
End If

ActiveCell.Offset(1, 0).Select
Counter = Counter + 1
Loop
Close
Next f '<=== CODE WORKS FINE IF BREAKPOINT IS PUT HERE!!
SummaryFile.Save '<=== OR EVEN HERE
'...the macro carries on - but that part works a-ok
Can anyone see why this is stalling? I've run out of ideas & can't even debug b/c the code DOES officially work... :huh:

Hope someone can help... Thx

Actually you could run this code step by step in the VBE with the F8.... use View Watch Window... to see what values are in your variables.... so you could figured out why your loop isn´t working...

Hi rick_deacha,
Thanks for the suggestion but I've already tried that and the code works perfectly when I step through it using F8 (just like it works when I use breakpoints). The values & results come out exactly as planned, 100% perfectly correct, so long as the code isn't run non-stop from start-to-finish.... But if I just press 'run', it stalls pretty quickly.
So clearly, the code works - it just won't run all the way through. It's as if VB is fine running the code in short bursts but that as soon as it's asked to run the whole thing, it gets 'scared' and shuts down.

So what would cause that??

It sounds like Excel or VBA might be running out of memory, or something similar.

Two suggestions: Make the code more efficient, and/or
Give Excel time to catch up

To make the code more efficient, get rid of all the ActiveCells and Selects etc., and refer to cells by address instead - Cells(rownum,colnum).Value = x.

To let Excel catch up, you could try adding DoEvents to your code, or possibly even let it Sleep for a moment. That should have roughly the same effect as pausing the code due to a breakpoint.

Thanks Herilane. I'll go try one (or both) of those right away and will report back later this afternoon.

ps: would boosting my computer's RAM help at all or is it a limitation within excel itself, regardless of how fast my RAM is?

I don't know, but I wouldn't buy more RAM in order to solve this. I think it's more likely to be an Excel problem.

I think I've worked this out but I'm going to fuss for another day, just in case. I'll post my solution (or next question) tomorrow.
Thanks for the help so far - you've been a huge help!

Oh and, with regard to my RAM question, I've found a good link & thought I'd post it for any one else who might be asking themselves the same thing: http://www.decisionmodels.com/memlimitsc.htm