Code skipping when screenupdating is turned off

[Guest]

I have several modules that run in succession. However, when I have screen
updating turned off it jumps over one small module. When I'm testing the
code and have screen updating turned on, it works fine. I have also tried
putting a breakpoint in the code with screenupdating turned off but it still
just skips over it.

I'm confuzzled.

 

[Don Guillett]

It might help if you posted your code..

 

[Guest]

Here's the call to this procedure with a preceding for next loop:

:::::::Screenupdating is turned off at the begining of this procedure.
For AA = 2 To TotResCount
AAStr = AA
Range("BN" + AAStr + "").Select
ActiveCell.Value = "=Sum(M" + AAStr + ":BM" + AAStr + ")"
Range("L" + AAStr + "").Activate
If ActiveCell.Offset(0, -6).Value = "Alloc" Then
ActiveCell.Value = "=Sum(BN" + AAStr + ")"
ActiveCell.Offset(0, -1).Value = "=Sum(L" + AAStr + " - J" +
AAStr + ")"
End If
Next AA
Application.Run "AddResSheets"

++++++++++THIS IS THE CODE IT SKIPS:

Public Sub AddResSheets()
Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
'Range("DataRange").Select
TtlRows = Selection.Rows.Count - 1

For a = 2 To TtlRows
Sheets("1-Consolidated").Activate
Range("A" + CStr(a) + "").Activate
ResName = ActiveCell.Value
If ResName <> ActiveCell.Offset(-1, 0) And ResName <> "Resource" Then
Range("A1:BN1").Select
Selection.Copy
Set NewSheet = Sheets.Add(Type:=xlWorksheet)
NewSheet.Name = ResName
x = 2
ActiveSheet.Paste
Range("A" + CStr(x) + "").Activate
ActiveCell.Value = "='1-Consolidated'!A" + CStr(a) + ""
Range("A" + CStr(x) + ": BN" + CStr(x) + "").Select
Selection.FillRight

ElseIf ResName <> "" Then
x = x + 1
Sheets("" + ResName + "").Activate
Range("A" + CStr(x) + "").Activate
ActiveCell.Value = "='1-Consolidated'!A" + CStr(a) + ""
Range("A" + CStr(x) + ": BN" + CStr(x) + "").Select
Selection.FillRight
End If

Next a
Application.Run "FrmWksheet"
End Sub

+++++++++and this is the follow on code:

Public Sub FrmWksheet()
Wrksht = 2
For Each W In Worksheets
:::::::lots of code
Next
End sub

 

[NickHK]

Screenupdating does not affect code execution only what is cureently
visible. So that is not the cause of your problem.
What is the value of TtlRows? More than 2 ?
Put a msgbox or "Debug.Assert False" statement at the top of the problem
routine to ensure it is actual fired.

Note also that if you really are concatenating strings (as opposed to adding
numbers), if is better to use "&" instead of "+".
Also, there is seldom any good reason to .select/.Activate object before
using them in Excel. e.g.

Range("BN" & AAStr).Value = "=Sum(M" & AAStr & ":BM" & AAStr & ")"
With Range("L" & AAStr)
If .Offset(0, -6).Value = "Alloc" Then
.Value = "=Sum(BN" & AAStr & ")"
'etc
End If
End With

NickHK