Macro Halting During Execution

K

Ken Hudson

As part of a large macro I have the code shown below. I have distributed the
macro to scores of users. For some of them the macro halts execution at the
last line of code shown below. They can click on the run sub/user form button
in the VB editor and the macro will then run to conclusion. Any ideas as to
why it would break for some folks at that line of code? I'm baffled.

For ILoop = 1 To ShCount
Sheets(ILoop).Activate
UndCount = 0
Rows(1).Delete
NumRows = Cells(Rows.Count, "C").End(xlUp).Row
'Get samples for UO and AP.
If (Left(ActiveSheet.Name, 3)) = "UND" Then
For Iloop2 = 1 To NumRows
If Application.CountA(Range("G" & Iloop2 & ":I" & Iloop2)) > 0
Then
UndCount = UndCount + 1
Cells(Iloop2, "J") = UndCount
UndTtl = UndTtl + Cells(Iloop2, "G") + Cells(Iloop2, "H") +
Cells(Iloop2, "I")
End If
Next Iloop2
If UndCount < 16 Then
For Iloop2 = 1 To NumRows
If Not IsEmpty(Cells(Iloop2, "J")) Then
Cells(Iloop2, "J") = "X"
End If
Next Iloop2
Else
Num = 15
t = UndCount
GenNUniqueRandom
For Iloop2 = 1 To Num
For Iloop3 = 1 To NumRows
If Cells(Iloop3, "J") = RndNo(Iloop2) Then
Cells(Iloop3, "J") = "X"
Exit For
End If
Next Iloop3
Next Iloop2
End If
Cells(NumRows + 2, "A") = "Total count of records > 90 days old"
Cells(NumRows + 2, "D") = UndCount
Cells(NumRows + 3, "A") = "Total dollar value of records > 90 days
old"
Cells(NumRows + 3, "D") = UndTtl
UndCount = 0
UndTtl = 0
Else
'Get samples for non-MCCF AR.
For Iloop2 = 1 To NumRows
If Application.CountA(Range("G" & Iloop2 & ":I" & Iloop2)) > 0
And _
Not IsEmpty(Cells(Iloop2, "B")) And _
IsNumeric(Mid(Cells(Iloop2, "C"), 8, 1)) Then
ARCount = ARCount + 1
ARTtl = ARTtl + Cells(Iloop2, "G") + Cells(Iloop2, "H") +
Cells(Iloop2, "I")
Cells(Iloop2, "J") = ARCount
End If
Next Iloop2
If ARCount < 11 Then
 
K

Ken Hudson

Hi,
I intentionally only pasted the code to the point where it halted execution.
Here is the whole section. It is halting at: "If ARCount < 11 Then".

ARCount = 0
ARTtl = 0
UndCount = 0
UndTtl = 0
ShCount = Sheets.Count
For ILoop = 1 To ShCount
Sheets(ILoop).Activate
UndCount = 0
Rows(1).Delete
NumRows = Cells(Rows.Count, "C").End(xlUp).Row
'Get samples for UO and AP.
If (Left(ActiveSheet.Name, 3)) = "UND" Then
For Iloop2 = 1 To NumRows
If Application.CountA(Range("G" & Iloop2 & ":I" & Iloop2)) > 0
Then
UndCount = UndCount + 1
Cells(Iloop2, "J") = UndCount
UndTtl = UndTtl + Cells(Iloop2, "G") + Cells(Iloop2, "H") +
Cells(Iloop2, "I")
End If
Next Iloop2
If UndCount < 16 Then
For Iloop2 = 1 To NumRows
If Not IsEmpty(Cells(Iloop2, "J")) Then
Cells(Iloop2, "J") = "X"
End If
Next Iloop2
Else
Num = 15
t = UndCount
GenNUniqueRandom
For Iloop2 = 1 To Num
For Iloop3 = 1 To NumRows
If Cells(Iloop3, "J") = RndNo(Iloop2) Then
Cells(Iloop3, "J") = "X"
Exit For
End If
Next Iloop3
Next Iloop2
End If
Cells(NumRows + 2, "A") = "Total count of records > 90 days old"
Cells(NumRows + 2, "D") = UndCount
Cells(NumRows + 3, "A") = "Total dollar value of records > 90 days
old"
Cells(NumRows + 3, "D") = UndTtl
UndCount = 0
UndTtl = 0
Else
'Get samples for non-MCCF AR.
For Iloop2 = 1 To NumRows
If Application.CountA(Range("G" & Iloop2 & ":I" & Iloop2)) > 0
And _
Not IsEmpty(Cells(Iloop2, "B")) And _
IsNumeric(Mid(Cells(Iloop2, "C"), 8, 1)) Then
ARCount = ARCount + 1
ARTtl = ARTtl + Cells(Iloop2, "G") + Cells(Iloop2, "H") +
Cells(Iloop2, "I")
Cells(Iloop2, "J") = ARCount
End If
Next Iloop2
If ARCount < 11 Then
For Iloop2 = 1 To NumRows
If Not IsEmpty(Cells(Iloop2, "J")) Then
Cells(Iloop2, "J") = "X"
End If
Next Iloop2
Else
Num = 10
t = ARCount
GenNUniqueRandom
For Iloop2 = 1 To Num
For Iloop3 = 1 To NumRows
If Cells(Iloop3, "J") = RndNo(Iloop2) Then
Cells(Iloop3, "J") = "X"
Exit For
End If
Next Iloop3
Next Iloop2
End If
Cells(NumRows + 2, "A") = "Total count of records > 90 days old"
Cells(NumRows + 2, "D") = ARCount
Cells(NumRows + 3, "A") = "Total dollar value of records > 90 days
old"
Cells(NumRows + 3, "D") = ARTtl
ARCount = 0
ARTtl = 0
End If
ActiveSheet.Copy after:=Sheets(Sheets.Count)
'Format sheet
Rows(1).Insert
Columns("A:B").HorizontalAlignment = xlLeft
Range("A1") = "Fund BFY"
Range("B1") = "ACC"
Range("C1") = "Doc ID"
Range("D1") = "< 30 days"
Range("E1") = "31-60 days"
Range("F1") = "61-90 days"
Range("G1") = "91-120 days"
Range("H1") = "121-180 days"
Range("I1") = "> 180 days"
Rows(1).Font.Bold = True
Rows(1).HorizontalAlignment = xlCenter
Columns("A:B").ColumnWidth = 13
Columns("C:I").ColumnWidth = 15
Columns("D:I").NumberFormat = "#,##0.00_);[Red](#,##0.00)"
'Highlight selected rows.
NumRows = Cells(Rows.Count, "C").End(xlUp).Row
For Iloop2 = 2 To NumRows
If Cells(Iloop2, "J") = "X" Then
Range("A" & Iloop2 & ":I" & Iloop2).Interior.ColorIndex = 4
End If
Next Iloop2
Columns("J").Delete
'Print set up options
With ActiveSheet.PageSetup
.PrintArea = "$A$1:$I" & NumRows
.PrintTitleRows = "$1:$1"
End With
ShName = Application.Proper(ActiveSheet.Name)
ShName = Left(ShName, Len(ShName) - 4) & " Detail"
PageSetupXL4M CenterHead:="&B&12FQAM Audit Samples - " & ShName & " for
" & Mo & ", FY" & Yr, _
LeftFoot:="&P of &N", RightFoot:="&D &T", LeftMarginInches:="0",
RightMarginInches:="0", _
PrintGridlines:="True", CenterHorizontally:="True",
Orientation:=xlLandscape
ActiveSheet.Name = ShName
Next ILoop
 
M

MacGuy

Ken, did you ever get an answer to this problem?

I'm having the same thing happen on one user's machine with no error code.
The same file ran last month (with last months data) no problem. By clicking
debug and then [F5] (run) the code will continue until completion. I checked
to see if it was giving an error number which it wasn't.
--
MacGuy


Ken Hudson said:
Hi,
I intentionally only pasted the code to the point where it halted execution.
Here is the whole section. It is halting at: "If ARCount < 11 Then".

ARCount = 0
ARTtl = 0
UndCount = 0
UndTtl = 0
ShCount = Sheets.Count
For ILoop = 1 To ShCount
Sheets(ILoop).Activate
UndCount = 0
Rows(1).Delete
NumRows = Cells(Rows.Count, "C").End(xlUp).Row
'Get samples for UO and AP.
If (Left(ActiveSheet.Name, 3)) = "UND" Then
For Iloop2 = 1 To NumRows
If Application.CountA(Range("G" & Iloop2 & ":I" & Iloop2)) > 0
Then
UndCount = UndCount + 1
Cells(Iloop2, "J") = UndCount
UndTtl = UndTtl + Cells(Iloop2, "G") + Cells(Iloop2, "H") +
Cells(Iloop2, "I")
End If
Next Iloop2
If UndCount < 16 Then
For Iloop2 = 1 To NumRows
If Not IsEmpty(Cells(Iloop2, "J")) Then
Cells(Iloop2, "J") = "X"
End If
Next Iloop2
Else
Num = 15
t = UndCount
GenNUniqueRandom
For Iloop2 = 1 To Num
For Iloop3 = 1 To NumRows
If Cells(Iloop3, "J") = RndNo(Iloop2) Then
Cells(Iloop3, "J") = "X"
Exit For
End If
Next Iloop3
Next Iloop2
End If
Cells(NumRows + 2, "A") = "Total count of records > 90 days old"
Cells(NumRows + 2, "D") = UndCount
Cells(NumRows + 3, "A") = "Total dollar value of records > 90 days
old"
Cells(NumRows + 3, "D") = UndTtl
UndCount = 0
UndTtl = 0
Else
'Get samples for non-MCCF AR.
For Iloop2 = 1 To NumRows
If Application.CountA(Range("G" & Iloop2 & ":I" & Iloop2)) > 0
And _
Not IsEmpty(Cells(Iloop2, "B")) And _
IsNumeric(Mid(Cells(Iloop2, "C"), 8, 1)) Then
ARCount = ARCount + 1
ARTtl = ARTtl + Cells(Iloop2, "G") + Cells(Iloop2, "H") +
Cells(Iloop2, "I")
Cells(Iloop2, "J") = ARCount
End If
Next Iloop2
If ARCount < 11 Then
For Iloop2 = 1 To NumRows
If Not IsEmpty(Cells(Iloop2, "J")) Then
Cells(Iloop2, "J") = "X"
End If
Next Iloop2
Else
Num = 10
t = ARCount
GenNUniqueRandom
For Iloop2 = 1 To Num
For Iloop3 = 1 To NumRows
If Cells(Iloop3, "J") = RndNo(Iloop2) Then
Cells(Iloop3, "J") = "X"
Exit For
End If
Next Iloop3
Next Iloop2
End If
Cells(NumRows + 2, "A") = "Total count of records > 90 days old"
Cells(NumRows + 2, "D") = ARCount
Cells(NumRows + 3, "A") = "Total dollar value of records > 90 days
old"
Cells(NumRows + 3, "D") = ARTtl
ARCount = 0
ARTtl = 0
End If
ActiveSheet.Copy after:=Sheets(Sheets.Count)
'Format sheet
Rows(1).Insert
Columns("A:B").HorizontalAlignment = xlLeft
Range("A1") = "Fund BFY"
Range("B1") = "ACC"
Range("C1") = "Doc ID"
Range("D1") = "< 30 days"
Range("E1") = "31-60 days"
Range("F1") = "61-90 days"
Range("G1") = "91-120 days"
Range("H1") = "121-180 days"
Range("I1") = "> 180 days"
Rows(1).Font.Bold = True
Rows(1).HorizontalAlignment = xlCenter
Columns("A:B").ColumnWidth = 13
Columns("C:I").ColumnWidth = 15
Columns("D:I").NumberFormat = "#,##0.00_);[Red](#,##0.00)"
'Highlight selected rows.
NumRows = Cells(Rows.Count, "C").End(xlUp).Row
For Iloop2 = 2 To NumRows
If Cells(Iloop2, "J") = "X" Then
Range("A" & Iloop2 & ":I" & Iloop2).Interior.ColorIndex = 4
End If
Next Iloop2
Columns("J").Delete
'Print set up options
With ActiveSheet.PageSetup
.PrintArea = "$A$1:$I" & NumRows
.PrintTitleRows = "$1:$1"
End With
ShName = Application.Proper(ActiveSheet.Name)
ShName = Left(ShName, Len(ShName) - 4) & " Detail"
PageSetupXL4M CenterHead:="&B&12FQAM Audit Samples - " & ShName & " for
" & Mo & ", FY" & Yr, _
LeftFoot:="&P of &N", RightFoot:="&D &T", LeftMarginInches:="0",
RightMarginInches:="0", _
PrintGridlines:="True", CenterHorizontally:="True",
Orientation:=xlLandscape
ActiveSheet.Name = ShName
Next ILoop

--
Ken Hudson


Ken Hudson said:
As part of a large macro I have the code shown below. I have distributed the
macro to scores of users. For some of them the macro halts execution at the
last line of code shown below. They can click on the run sub/user form button
in the VB editor and the macro will then run to conclusion. Any ideas as to
why it would break for some folks at that line of code? I'm baffled.

For ILoop = 1 To ShCount
Sheets(ILoop).Activate
UndCount = 0
Rows(1).Delete
NumRows = Cells(Rows.Count, "C").End(xlUp).Row
'Get samples for UO and AP.
If (Left(ActiveSheet.Name, 3)) = "UND" Then
For Iloop2 = 1 To NumRows
If Application.CountA(Range("G" & Iloop2 & ":I" & Iloop2)) > 0
Then
UndCount = UndCount + 1
Cells(Iloop2, "J") = UndCount
UndTtl = UndTtl + Cells(Iloop2, "G") + Cells(Iloop2, "H") +
Cells(Iloop2, "I")
End If
Next Iloop2
If UndCount < 16 Then
For Iloop2 = 1 To NumRows
If Not IsEmpty(Cells(Iloop2, "J")) Then
Cells(Iloop2, "J") = "X"
End If
Next Iloop2
Else
Num = 15
t = UndCount
GenNUniqueRandom
For Iloop2 = 1 To Num
For Iloop3 = 1 To NumRows
If Cells(Iloop3, "J") = RndNo(Iloop2) Then
Cells(Iloop3, "J") = "X"
Exit For
End If
Next Iloop3
Next Iloop2
End If
Cells(NumRows + 2, "A") = "Total count of records > 90 days old"
Cells(NumRows + 2, "D") = UndCount
Cells(NumRows + 3, "A") = "Total dollar value of records > 90 days
old"
Cells(NumRows + 3, "D") = UndTtl
UndCount = 0
UndTtl = 0
Else
'Get samples for non-MCCF AR.
For Iloop2 = 1 To NumRows
If Application.CountA(Range("G" & Iloop2 & ":I" & Iloop2)) > 0
And _
Not IsEmpty(Cells(Iloop2, "B")) And _
IsNumeric(Mid(Cells(Iloop2, "C"), 8, 1)) Then
ARCount = ARCount + 1
ARTtl = ARTtl + Cells(Iloop2, "G") + Cells(Iloop2, "H") +
Cells(Iloop2, "I")
Cells(Iloop2, "J") = ARCount
End If
Next Iloop2
If ARCount < 11 Then
Click to expand...
Click to expand...
 
K

Ken Hudson

I dind't get an answer but I think I found the answer to the problem, at
least it worked for me.
I simply deleted the offending line of code, even though it looked correct,
and re-entered it. The macro ran smoothly after that.
Hope it works for you.
--
Ken Hudson


MacGuy said:
Ken, did you ever get an answer to this problem?

I'm having the same thing happen on one user's machine with no error code.
The same file ran last month (with last months data) no problem. By clicking
debug and then [F5] (run) the code will continue until completion. I checked
to see if it was giving an error number which it wasn't.
--
MacGuy


Ken Hudson said:
Hi,
I intentionally only pasted the code to the point where it halted execution.
Here is the whole section. It is halting at: "If ARCount < 11 Then".

ARCount = 0
ARTtl = 0
UndCount = 0
UndTtl = 0
ShCount = Sheets.Count
For ILoop = 1 To ShCount
Sheets(ILoop).Activate
UndCount = 0
Rows(1).Delete
NumRows = Cells(Rows.Count, "C").End(xlUp).Row
'Get samples for UO and AP.
If (Left(ActiveSheet.Name, 3)) = "UND" Then
For Iloop2 = 1 To NumRows
If Application.CountA(Range("G" & Iloop2 & ":I" & Iloop2)) > 0
Then
UndCount = UndCount + 1
Cells(Iloop2, "J") = UndCount
UndTtl = UndTtl + Cells(Iloop2, "G") + Cells(Iloop2, "H") +
Cells(Iloop2, "I")
End If
Next Iloop2
If UndCount < 16 Then
For Iloop2 = 1 To NumRows
If Not IsEmpty(Cells(Iloop2, "J")) Then
Cells(Iloop2, "J") = "X"
End If
Next Iloop2
Else
Num = 15
t = UndCount
GenNUniqueRandom
For Iloop2 = 1 To Num
For Iloop3 = 1 To NumRows
If Cells(Iloop3, "J") = RndNo(Iloop2) Then
Cells(Iloop3, "J") = "X"
Exit For
End If
Next Iloop3
Next Iloop2
End If
Cells(NumRows + 2, "A") = "Total count of records > 90 days old"
Cells(NumRows + 2, "D") = UndCount
Cells(NumRows + 3, "A") = "Total dollar value of records > 90 days
old"
Cells(NumRows + 3, "D") = UndTtl
UndCount = 0
UndTtl = 0
Else
'Get samples for non-MCCF AR.
For Iloop2 = 1 To NumRows
If Application.CountA(Range("G" & Iloop2 & ":I" & Iloop2)) > 0
And _
Not IsEmpty(Cells(Iloop2, "B")) And _
IsNumeric(Mid(Cells(Iloop2, "C"), 8, 1)) Then
ARCount = ARCount + 1
ARTtl = ARTtl + Cells(Iloop2, "G") + Cells(Iloop2, "H") +
Cells(Iloop2, "I")
Cells(Iloop2, "J") = ARCount
End If
Next Iloop2
If ARCount < 11 Then
For Iloop2 = 1 To NumRows
If Not IsEmpty(Cells(Iloop2, "J")) Then
Cells(Iloop2, "J") = "X"
End If
Next Iloop2
Else
Num = 10
t = ARCount
GenNUniqueRandom
For Iloop2 = 1 To Num
For Iloop3 = 1 To NumRows
If Cells(Iloop3, "J") = RndNo(Iloop2) Then
Cells(Iloop3, "J") = "X"
Exit For
End If
Next Iloop3
Next Iloop2
End If
Cells(NumRows + 2, "A") = "Total count of records > 90 days old"
Cells(NumRows + 2, "D") = ARCount
Cells(NumRows + 3, "A") = "Total dollar value of records > 90 days
old"
Cells(NumRows + 3, "D") = ARTtl
ARCount = 0
ARTtl = 0
End If
ActiveSheet.Copy after:=Sheets(Sheets.Count)
'Format sheet
Rows(1).Insert
Columns("A:B").HorizontalAlignment = xlLeft
Range("A1") = "Fund BFY"
Range("B1") = "ACC"
Range("C1") = "Doc ID"
Range("D1") = "< 30 days"
Range("E1") = "31-60 days"
Range("F1") = "61-90 days"
Range("G1") = "91-120 days"
Range("H1") = "121-180 days"
Range("I1") = "> 180 days"
Rows(1).Font.Bold = True
Rows(1).HorizontalAlignment = xlCenter
Columns("A:B").ColumnWidth = 13
Columns("C:I").ColumnWidth = 15
Columns("D:I").NumberFormat = "#,##0.00_);[Red](#,##0.00)"
'Highlight selected rows.
NumRows = Cells(Rows.Count, "C").End(xlUp).Row
For Iloop2 = 2 To NumRows
If Cells(Iloop2, "J") = "X" Then
Range("A" & Iloop2 & ":I" & Iloop2).Interior.ColorIndex = 4
End If
Next Iloop2
Columns("J").Delete
'Print set up options
With ActiveSheet.PageSetup
.PrintArea = "$A$1:$I" & NumRows
.PrintTitleRows = "$1:$1"
End With
ShName = Application.Proper(ActiveSheet.Name)
ShName = Left(ShName, Len(ShName) - 4) & " Detail"
PageSetupXL4M CenterHead:="&B&12FQAM Audit Samples - " & ShName & " for
" & Mo & ", FY" & Yr, _
LeftFoot:="&P of &N", RightFoot:="&D &T", LeftMarginInches:="0",
RightMarginInches:="0", _
PrintGridlines:="True", CenterHorizontally:="True",
Orientation:=xlLandscape
ActiveSheet.Name = ShName
Next ILoop

--
Ken Hudson


Ken Hudson said:
As part of a large macro I have the code shown below. I have distributed the
macro to scores of users. For some of them the macro halts execution at the
last line of code shown below. They can click on the run sub/user form button
in the VB editor and the macro will then run to conclusion. Any ideas as to
why it would break for some folks at that line of code? I'm baffled.

For ILoop = 1 To ShCount
Sheets(ILoop).Activate
UndCount = 0
Rows(1).Delete
NumRows = Cells(Rows.Count, "C").End(xlUp).Row
'Get samples for UO and AP.
If (Left(ActiveSheet.Name, 3)) = "UND" Then
For Iloop2 = 1 To NumRows
If Application.CountA(Range("G" & Iloop2 & ":I" & Iloop2)) > 0
Then
UndCount = UndCount + 1
Cells(Iloop2, "J") = UndCount
UndTtl = UndTtl + Cells(Iloop2, "G") + Cells(Iloop2, "H") +
Cells(Iloop2, "I")
End If
Next Iloop2
If UndCount < 16 Then
For Iloop2 = 1 To NumRows
If Not IsEmpty(Cells(Iloop2, "J")) Then
Cells(Iloop2, "J") = "X"
End If
Next Iloop2
Else
Num = 15
t = UndCount
GenNUniqueRandom
For Iloop2 = 1 To Num
For Iloop3 = 1 To NumRows
If Cells(Iloop3, "J") = RndNo(Iloop2) Then
Cells(Iloop3, "J") = "X"
Exit For
End If
Next Iloop3
Next Iloop2
End If
Cells(NumRows + 2, "A") = "Total count of records > 90 days old"
Cells(NumRows + 2, "D") = UndCount
Cells(NumRows + 3, "A") = "Total dollar value of records > 90 days
old"
Cells(NumRows + 3, "D") = UndTtl
UndCount = 0
UndTtl = 0
Else
'Get samples for non-MCCF AR.
For Iloop2 = 1 To NumRows
If Application.CountA(Range("G" & Iloop2 & ":I" & Iloop2)) > 0
And _
Not IsEmpty(Cells(Iloop2, "B")) And _
IsNumeric(Mid(Cells(Iloop2, "C"), 8, 1)) Then
ARCount = ARCount + 1
ARTtl = ARTtl + Cells(Iloop2, "G") + Cells(Iloop2, "H") +
Cells(Iloop2, "I")
Cells(Iloop2, "J") = ARCount
End If
Next Iloop2
If ARCount < 11 Then
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

For..Next error 'End if without block if' 6
Conditional borders 3
append multiple worksheets data into a single multi-dimensional array 4
Text to Rows 2
Subscript out of range, and more 5
Loop with Step 1
Variable counting of rows 7
Adding Zeros in front of numbers in cells using Excel 2003 14

Top