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

My VBA code works in the step though mode but not at full spee
 

your code is impossible to replicate without the actual sheets

however:
i'm wondering about the necessity of repeating
application.screenupdating=false

also the repeating msgbox lines (commented)
indicate that you haven't studied vba end skipped any chapters on
debugging. Ever heard of VBE's local window?





--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Nicole B wrote :

I'm still looking for a response if anyone can help me. Thanks!

My VBA code works in the step though mode but not at full spee
 

OK, since my code cannot be replicated, I'll rephrase. Is it possible to
write something that interrupts a procedure and then starts back at a
different point in a procedure? I think that that would work, if it can be
done.

I had to repeat application.screenupdating = false because the procedures
that I'm calling turn it back on at the end of the procedure. I guess I
could set it up so that those procedures only turn it back on if it was on at
the beginning of the other procedure. Would that be more efficient?

vbe's local window won't tell me what sheet it's on, or at least I don't
know how to make it do so, so it wasn't helpful while debugging. the
msgboxes were more explicit. Unless I've missed something.

And no, I haven't studied VBA properly, that's why I'm asking questions.

"keepITcool" wrote:

your code is impossible to replicate without the actual sheets

however:
i'm wondering about the necessity of repeating
application.screenupdating=false

also the repeating msgbox lines (commented)
indicate that you haven't studied vba end skipped any chapters on
debugging. Ever heard of VBE's local window?





--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Nicole B wrote :

I'm still looking for a response if anyone can help me. Thanks!

My VBA code works in the step though mode but not at full spee
 

please read back.. why should I answer this


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Nicole B wrote :

Unless I've missed
something.

And no, I haven't studied VBA properly, that's why I'm asking
questions.

My VBA code works in the step though mode but not at full spee
 

just kidding.

i think your code probably goes wrong because you use unqualified
addressing. also it is unclear which is the activesheet

and you mix Activesheet.Range
Worksheets(index).range
Range

when LastCell is an unqualifeed address (just a simple $A$1)
Range(lastcell) will get you a range on the activesheet

But I'm not sure what Range(lastcell,"m10") will get you.

this one is interesting:

LastCell = Worksheets(Index).Range("l65536"). _
End(xlUp).Offset(-3,1).Address
ShippedQty = ActiveSheet.Range(LastCell, "m10").Address

LastCellNext = Range(LastCell).Offset(3, -1).Address
Do While Worksheets(Index).Range(LastCellNext).HasFormula
Range(ShippedQty).Locked = False
ShippedQty = Range(ShippedQty).Offset(0, 3).Address
LastCellNext = Range(LastCellNext).Offset(0, 3).Address
Loop

your addresses come from a mix of cells on the activeworksheet
and on worksheets(index), therefor you may get unpredictable results
and anyone will have a hard time figuring out what your doing.

if I read and interpret your code following should do the same

dim wks,rng
set wks = worksheets(index)
set rng= wks.Range("l65536").end(xlUp)

while rng.offset(0,3).hasformula
set rng=rng.offset(0,3)
wend
wks.range(rng,"m10").locked = false



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


keepITcool wrote :


please read back.. why should I answer this


--
keepITcool
www.XLsupport.com | keepITcool chello nl | amsterdam


Nicole B wrote :

Unless I've missed
something.

And no, I haven't studied VBA properly, that's why I'm asking
questions.

My VBA code works in the step though mode but not at full spee
 

Thanks, that was helpful! Oh, I read the chapter on debugging. I'm using
the watch window now, and it's much betterthan a thousand message boxes!