Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
My VBA code works in the step though mode but not at full speed
I may be having the same problem. I'm having problem with the
EditShippedMaterials macro. It works if I run it in 2 parts, but I have to actually manuall step through. I usually debug-run to cursor at the first asterisk in my EditShippedMaterials sub (at the for loop) and then step through the rest. Basicially, I have to interrupt the code for it to work, as far as I can tell. It won't work if I just run the macro. Actually, if I just step through it, without interrupting the flow and restarting the debugging at the asterisk, it skips the where loop altogether. Here's my code (it's a little complex, but here goes:) (I've included the two macros that are called by it in case it helps). Public Sub EditShippedMaterials() Application.ScreenUpdating = False Call WorkbookUnprotect Application.ScreenUpdating = False Call modFilterEstimateData.RemoveEstimateFilter Application.ScreenUpdating = False Dim LastCell As String, LastCellNext As String, ShippedQty As String Index = 0 * For Index = 2 To 12 LastCell = Worksheets(Index).Range("l65536").End(xlUp).Offset (-3, 1).Address ' MsgBox LastCell 'MsgBox i & Worksheets(Index).Name ShippedQty = ActiveSheet.Range(LastCell, "m10").Address 'MsgBox ShippedQty & Worksheets(Index).Name LastCellNext = Range(LastCell).Offset(3, -1).Address 'MsgBox "Sheet " & Index & ":" & Worksheets(Index).Name & LastCellNext Do While Worksheets(Index).Range(LastCellNext).HasFormula Range(ShippedQty).Locked = False ' If Range(ShippedQty).Locked = False Then MsgBox Index & " " & Worksheets(Index).Name & " " & ShippedQty & " unlocked" ShippedQty = Range(ShippedQty).Offset(0, 3).Address ' MsgBox ShippedQty LastCellNext = Range(LastCellNext).Offset(0, 3).Address ' MsgBox LastCellNext Loop '?? Next Index If wkshtLumberShores.Range("m11").Locked = False Then MsgBox "The Shipped cells are ready for editing." End Sub Public Sub FilterEstimateData() If wkshtLumberShores.FilterMode = False Then Application.StatusBar = "Filtering Estimate Data" With wkshtLumberShores.Range("TLumberEstQty", "BLumberEstQty") .Locked = True .Offset(0, -1).Locked = True End With With wkshtLumberShores.Range("TShoresEstQty", "BShoresEstQty") .Locked = True .Offset(0, -1).Locked = True End With wkshtLumberShores.Range("TLumberEstQty", "BShoresEstQty") _ .AutoFilter Field:=1, Criteria1:="0", Operator:=xlOr, _ Criteria2:="<", VisibleDropDown:=False With wkshtPlywoodHardware.Range("TPlywoodEstQty", "BPlywoodEstQty") .Locked = True .Offset(0, -1).Locked = True End With With wkshtPlywoodHardware.Range("THardwareEstQty", "BHardwareEstQty") .Locked = True .Offset(0, -1).Locked = True End With wkshtPlywoodHardware.Range("TPlywoodEstQty", "BHardwareEstQty") _ .AutoFilter Field:=1, Criteria1:="0", Operator:=xlOr, _ Criteria2:="<", VisibleDropDown:=False With wkshtScaffold.Range("TScaffoldEstQty", "BScaffoldEstQty") .Locked = True .Offset(0, -1).Locked = True .AutoFilter Field:=1, Criteria1:="0", Operator:=xlOr, _ Criteria2:="<", VisibleDropDown:=False End With With wkshtPurlinsRunners.Range("TPurlinsRunnersEstQty", "BPurlinsrunnersEstQty") .Locked = True .Offset(0, -1).Locked = True .AutoFilter Field:=1, Criteria1:="0", Operator:=xlOr, _ Criteria2:="<", VisibleDropDown:=False End With With wkshtHVDoka.Range("THVDokaEstQty", "BHVDokaEstQty") .Locked = True .Offset(0, -1).Locked = True .AutoFilter Field:=1, Criteria1:="0", Operator:=xlOr, _ Criteria2:="<", VisibleDropDown:=False End With With wkshtCecoWallParts.Range("TCecoWallsEstQty", "BCecoWallsEstQty") .Locked = True .Offset(0, -1).Locked = True .AutoFilter Field:=1, Criteria1:="0", Operator:=xlOr, _ Criteria2:="<", VisibleDropDown:=False End With With wkshtBurkeHandset.Range("TBurkeHandsetEstQty", "BBurkeHandsetEstQty") .Locked = True .Offset(0, -1).Locked = True .AutoFilter Field:=1, Criteria1:="0", Operator:=xlOr, _ Criteria2:="<", VisibleDropDown:=False End With With wkshtBurkeCraneset.Range("TBurkeCranesetEstQty", "BBurkeCranesetEstQty") .Locked = True .Offset(0, -1).Locked = True End With With wkshtPansEquipment.Range("TPansEquipEstQty", "BPansEquipEstQty") .Locked = True .Offset(0, -1).Locked = True End With With wkshtTrussParts.Range("TTrussPartsEstQty", "BTrussPartsEstQty") .Locked = True .Offset(0, -1).Locked = True .AutoFilter Field:=1, Criteria1:="0", Operator:=xlOr, _ Criteria2:="<", VisibleDropDown:=False End With With wkshtTools.Range("TToolsShippedQty", "BToolsShippedQty") .Locked = True .Offset(0, -1).Locked = True .AutoFilter Field:=1, Criteria1:="0", _ VisibleDropDown:=False End With wkshtTools.Range("ToolsLegend").EntireRow.Hidden = True End If Application.ScreenUpdating = True Application.StatusBar = False End Sub Public Sub WorkbookUnprotect() If wkshtInventoryHome.ProtectContents = True Then Application.ScreenUpdating = False ActiveWorkbook.Unprotect Password:="ceco24" wkshtInventoryHome.Activate For Index = 1 To 13 Worksheets(Index).Unprotect Password:="password" Next Application.ScreenUpdating = True End If End Sub |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
full screen mode | Excel Discussion (Misc queries) | |||
My VBA code works in the step though mode but not at full speed | Excel Programming | |||
Works if I single step | Excel Programming | |||
Runs Fine In Step Mode But Crashes Excel Otherwise | Excel Programming | |||
Debug Step vs Run Mode | Excel Programming |