Application Problem
Find has several persistent values. You are only setting the search target,
so some previous use of find could set one of the other values so that the find is not made. xlwhole vice xlpart, xlformulas vice xlvalues for example can affect whether the target is found. If it is a date, then it can get even more complicated. -- Regards, Tom Ogilvy Stuart wrote in message ... I am having an intermittant problem with some VBA that I am unable to resolve, and write in hope that someone can point me in the right direction! The following two lines of code occasionally fail to find what is there! Sheets("VS").Columns("B").Find(what:=rng).Offset(0 , 8) = Sheets("VS").Columns("B").Find(what:=rng).Offset(0 , 8) + rng.Offset(0, 4) Application.StatusBar = Cells(Target.Row, 3) & " Changed from " & Sheets("VS").Columns("B").Find(what:=Cells(Target. Row, 3), LookAt:=xlWhole).Offset(0, ofSt) Please note, other "Fnd" commands work ok when the above two lines stop working! These lines of code are in seperate macros in a substantial workbook that has been wrote over many years and performs faultlessly 95% of the time, however, occasionally the above lines stops working. The problem is rectified by closing the entire application down then reopening the application and workbook. Everything will then work fine until the next time it curiously stops. I have noted below the two subs that these lines are in. Note these are onlt two macros out of about 80 in this workbook. Sub showStocka() Dim totI, totO, totC, totT, totR, totV, cnt, anChor Application.EnableEvents = False Application.ScreenUpdating = False 'initial tests for records If Len(Sheets("Reference").Range("C2").Offset(Sheets( "Reference").Range("C2") + 1, 1)) < 11 Then MsgBox "No Stock Records" Application.EnableEvents = True Exit Sub End If On Error Resume Next Sheets("SS").Select Columns("I:I").Find(what:=Sheets("Reference").Rang e("C2").Offset(Sheets("Ref erence").Range("C2") + 1, 1)).Select If Err Then MsgBox "Macro Problem, main reference not found on stock sheet" Sheets("Stock Control").Select Application.EnableEvents = True Exit Sub End If On Error GoTo 0 'prepare VS sheet and copy in data Sheets("VS").Select ActiveSheet.Unprotect Range("$A$1", Selection.SpecialCells(xlLastCell)).ClearContents Sheets("SS").Select Range(Cells(ActiveCell.Row, 1), Cells(ActiveCell.Row, 1).End(xlDown).Offset(0, 29)).Select Selection.Copy Sheets("VS").Select Range("B3").PasteSpecial Paste:=xlValues Sheets("SS").Range("P1:AD1").Copy Range("Q3").PasteSpecial Paste:=xlValues anChor = Range("B3").End(xlDown).Offset(1, 0).Address Sheets("OX").Select Range(Range("A1"), Range("A30000").End(xlUp)).Select cnt = 0 For Each rng In Selection If Len(rng) = 5 And Left(rng, 2) = Sheets("VS").Range("C3") Then On Error Resume Next Sheets("VS").Columns("B").Find(what:=rng).Offset(0 , 8) = Sheets("VS").Columns("B").Find(what:=rng).Offset(0 , 8) + rng.Offset(0, 4) If Err Then On Error GoTo 0 Sheets("VS").Range(anChor).Offset(cnt, 0) = rng Sheets("VS").Range(anChor).Offset(cnt, 1) = rng.Offset(0, 1) Sheets("VS").Range(anChor).Offset(cnt, 2) = rng.Offset(0, 2) Sheets("VS").Range(anChor).Offset(cnt, 3) = rng.Offset(0, 3) Sheets("VS").Range(anChor).Offset(cnt, 8) = rng.Offset(0, 4) Sheets("VS").Range(anChor).Offset(cnt, 11) = rng.Offset(0, 7) Sheets("VS").Range(anChor).Offset(cnt, 12) = rng.Offset(0, 8) Sheets("VS").Range(anChor).Offset(cnt, 13) = "N" cnt = cnt + 1 End If On Error GoTo 0 End If Next rng Sheets("VS").Select Range("B4").Select If Range("B5") < "" Then Range("B4", Cells(4, 2).End(xlDown)).Select totV = 0: totI = 0: totO = 0: totC = 0: totR = 0: totT = 0 For Each rng In Selection rng.Offset(0, -1) = Right(rng, 3) / 1 totV = totV + rng.Offset(0, 9) * rng.Offset(0, 11) totT = totT + rng.Offset(0, 11) totR = totR + rng.Offset(0, 12) If rng.Offset(0, 9) 0 Then totI = totI + 1 Else totO = totO + 1 End If If rng.Offset(0, 10) = "X" Then totC = totC + 1 Next rng Range("A1") = totV Range("B1") = totI Range("C1") = totO Range("D1") = totC Range("E1") = totR / totT 'sets view Columns("E").ColumnWidth = 0 Columns("F").ColumnWidth = 0 Columns("Q").ColumnWidth = 0 Columns("H").ColumnWidth = 0 Range("A4:AA4").Select ActiveWindow.Zoom = True If Range("A5") < "" Then Range("A4", Cells(4, 1).End(xlDown).Offset(0, 31)).Select Range("A4", Cells(4, 1).End(xlDown).Offset(0, 31)).Sort Key1:=Range("A4"), Order1:=xlAscending End If Range("A2") = "A4" 'see sort routine Range("A4").Select ActiveSheet.DrawingObjects("ModeBox").Characters.T ext = "View Only" ActiveSheet.DrawingObjects("ViewOnlyButGroup").Bri ngToFront ActiveSheet.DrawingObjects("EditViewButGroup").Sen dToBack ActiveSheet.DrawingObjects("OrderButGroup").SendTo Back ActiveSheet.DrawingObjects("But_ViewOrder").SendTo Back Columns("A:AE").Locked = True ActiveSheet.Protect Application.OnTime Now, "fixView" With ActiveWindow .DisplayHeadings = False .DisplayHorizontalScrollBar = False .DisplayWorkbookTabs = False .DisplayVerticalScrollBar = True End With With Application .DisplayFormulaBar = False .DisplayStatusBar = True End With Application.EnableEvents = True glb_LineOnOff = 0 End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) Dim ofSt As Integer If Target.Interior.ColorIndex = 36 Then If Target.Column 6 Then ofSt = Target.Column + 1 Else ofSt = Target.Column - 3 End If Application.StatusBar = Cells(Target.Row, 3) & " Changed from " & Sheets("VS").Columns("B").Find(what:=Cells(Target. Row, 3), LookAt:=xlWhole).Offset(0, ofSt) Else Application.StatusBar = False End If End Sub |
Application Problem
I am having an intermittant problem with some VBA that I am unable to resolve, and write in hope that someone can point me in the right direction! The following two lines of code occasionally fail to find what is there! Sheets("VS").Columns("B").Find(what:=rng).Offset(0 , 8) = Sheets("VS").Columns("B").Find(what:=rng).Offset(0 , 8) + rng.Offset(0, 4) Application.StatusBar = Cells(Target.Row, 3) & " Changed from " & Sheets("VS").Columns("B").Find(what:=Cells(Target. Row, 3), LookAt:=xlWhole).Offset(0, ofSt) Please note, other "Fnd" commands work ok when the above two lines stop working! These lines of code are in seperate macros in a substantial workbook that has been wrote over many years and performs faultlessly 95% of the time, however, occasionally the above lines stops working. The problem is rectified by closing the entire application down then reopening the application and workbook. Everything will then work fine until the next time it curiously stops. I have noted below the two subs that these lines are in. Note these are onlt two macros out of about 80 in this workbook. Sub showStocka() Dim totI, totO, totC, totT, totR, totV, cnt, anChor Application.EnableEvents = False Application.ScreenUpdating = False 'initial tests for records If Len(Sheets("Reference").Range("C2").Offset(Sheets( "Reference").Range("C2") + 1, 1)) < 11 Then MsgBox "No Stock Records" Application.EnableEvents = True Exit Sub End If On Error Resume Next Sheets("SS").Select Columns("I:I").Find(what:=Sheets("Reference").Rang e("C2").Offset(Sheets("Ref erence").Range("C2") + 1, 1)).Select If Err Then MsgBox "Macro Problem, main reference not found on stock sheet" Sheets("Stock Control").Select Application.EnableEvents = True Exit Sub End If On Error GoTo 0 'prepare VS sheet and copy in data Sheets("VS").Select ActiveSheet.Unprotect Range("$A$1", Selection.SpecialCells(xlLastCell)).ClearContents Sheets("SS").Select Range(Cells(ActiveCell.Row, 1), Cells(ActiveCell.Row, 1).End(xlDown).Offset(0, 29)).Select Selection.Copy Sheets("VS").Select Range("B3").PasteSpecial Paste:=xlValues Sheets("SS").Range("P1:AD1").Copy Range("Q3").PasteSpecial Paste:=xlValues anChor = Range("B3").End(xlDown).Offset(1, 0).Address Sheets("OX").Select Range(Range("A1"), Range("A30000").End(xlUp)).Select cnt = 0 For Each rng In Selection If Len(rng) = 5 And Left(rng, 2) = Sheets("VS").Range("C3") Then On Error Resume Next Sheets("VS").Columns("B").Find(what:=rng).Offset(0 , 8) = Sheets("VS").Columns("B").Find(what:=rng).Offset(0 , 8) + rng.Offset(0, 4) If Err Then On Error GoTo 0 Sheets("VS").Range(anChor).Offset(cnt, 0) = rng Sheets("VS").Range(anChor).Offset(cnt, 1) = rng.Offset(0, 1) Sheets("VS").Range(anChor).Offset(cnt, 2) = rng.Offset(0, 2) Sheets("VS").Range(anChor).Offset(cnt, 3) = rng.Offset(0, 3) Sheets("VS").Range(anChor).Offset(cnt, 8) = rng.Offset(0, 4) Sheets("VS").Range(anChor).Offset(cnt, 11) = rng.Offset(0, 7) Sheets("VS").Range(anChor).Offset(cnt, 12) = rng.Offset(0, 8) Sheets("VS").Range(anChor).Offset(cnt, 13) = "N" cnt = cnt + 1 End If On Error GoTo 0 End If Next rng Sheets("VS").Select Range("B4").Select If Range("B5") < "" Then Range("B4", Cells(4, 2).End(xlDown)).Select totV = 0: totI = 0: totO = 0: totC = 0: totR = 0: totT = 0 For Each rng In Selection rng.Offset(0, -1) = Right(rng, 3) / 1 totV = totV + rng.Offset(0, 9) * rng.Offset(0, 11) totT = totT + rng.Offset(0, 11) totR = totR + rng.Offset(0, 12) If rng.Offset(0, 9) 0 Then totI = totI + 1 Else totO = totO + 1 End If If rng.Offset(0, 10) = "X" Then totC = totC + 1 Next rng Range("A1") = totV Range("B1") = totI Range("C1") = totO Range("D1") = totC Range("E1") = totR / totT 'sets view Columns("E").ColumnWidth = 0 Columns("F").ColumnWidth = 0 Columns("Q").ColumnWidth = 0 Columns("H").ColumnWidth = 0 Range("A4:AA4").Select ActiveWindow.Zoom = True If Range("A5") < "" Then Range("A4", Cells(4, 1).End(xlDown).Offset(0, 31)).Select Range("A4", Cells(4, 1).End(xlDown).Offset(0, 31)).Sort Key1:=Range("A4"), Order1:=xlAscending End If Range("A2") = "A4" 'see sort routine Range("A4").Select ActiveSheet.DrawingObjects("ModeBox").Characters.T ext = "View Only" ActiveSheet.DrawingObjects("ViewOnlyButGroup").Bri ngToFront ActiveSheet.DrawingObjects("EditViewButGroup").Sen dToBack ActiveSheet.DrawingObjects("OrderButGroup").SendTo Back ActiveSheet.DrawingObjects("But_ViewOrder").SendTo Back Columns("A:AE").Locked = True ActiveSheet.Protect Application.OnTime Now, "fixView" With ActiveWindow .DisplayHeadings = False .DisplayHorizontalScrollBar = False .DisplayWorkbookTabs = False .DisplayVerticalScrollBar = True End With With Application .DisplayFormulaBar = False .DisplayStatusBar = True End With Application.EnableEvents = True glb_LineOnOff = 0 End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) Dim ofSt As Integer If Target.Interior.ColorIndex = 36 Then If Target.Column 6 Then ofSt = Target.Column + 1 Else ofSt = Target.Column - 3 End If Application.StatusBar = Cells(Target.Row, 3) & " Changed from " & Sheets("VS").Columns("B").Find(what:=Cells(Target. Row, 3), LookAt:=xlWhole).Offset(0, ofSt) Else Application.StatusBar = False End If End Sub |
Application Problem
I will give that a go next time the problem occurs, however, I doubt that is
the answer, as it performs a very simillar find with the same settings on a very simillar piece of date 10 lines before the problem line! And why won't it work properly again until the Application is reStarted, simply reStarting the workbook makes no differance! Tom Ogilvy wrote in message ... Find has several persistent values. You are only setting the search target, so some previous use of find could set one of the other values so that the find is not made. xlwhole vice xlpart, xlformulas vice xlvalues for example can affect whether the target is found. If it is a date, then it can get even more complicated. -- Regards, Tom Ogilvy |
Application Problem
Hopefully you will get a more meaningful answer from someone more
knowledgeable. -- Regards, Tom Ogilvy Stuart wrote in message ... I will give that a go next time the problem occurs, however, I doubt that is the answer, as it performs a very simillar find with the same settings on a very simillar piece of date 10 lines before the problem line! And why won't it work properly again until the Application is reStarted, simply reStarting the workbook makes no differance! Tom Ogilvy wrote in message ... Find has several persistent values. You are only setting the search target, so some previous use of find could set one of the other values so that the find is not made. xlwhole vice xlpart, xlformulas vice xlvalues for example can affect whether the target is found. If it is a date, then it can get even more complicated. -- Regards, Tom Ogilvy |
Application Problem
Stuart
is "performs faultlessly 95% of the time" acceptable in your world ? If it is, why worry ? If it's not, I would heed the advice that is offered, apply the recommendations and determine if this gives you 100% success ... which would be my definition of faultless. Tom seems not to be easily offended ... which is probably as well when you so easily dismiss his advice. Maybe he'll think twice before attempting to answer your next problem. Regards Trevor "Stuart" wrote in message ... I will give that a go next time the problem occurs, however, I doubt that is the answer, as it performs a very simillar find with the same settings on a very simillar piece of date 10 lines before the problem line! And why won't it work properly again until the Application is reStarted, simply reStarting the workbook makes no differance! Tom Ogilvy wrote in message ... Find has several persistent values. You are only setting the search target, so some previous use of find could set one of the other values so that the find is not made. xlwhole vice xlpart, xlformulas vice xlvalues for example can affect whether the target is found. If it is a date, then it can get even more complicated. -- Regards, Tom Ogilvy |
Application Problem
Trevor Shuttleworth wrote in message ... Stuart is "performs faultlessly 95% of the time" acceptable in your world ? No.....that's why I'm asking for help If it is, why worry ? If it's not, I would heed the advice that is offered, apply the recommendations and determine if this gives you 100% success ... which would be my definition of faultless. The recomendations Tom offered will be tried the next time the problem occurs, nobody hopes more than me that the remedy could be so simple. However, I see no harm in me questioning Tom's advice! Tom seems not to be easily offended ... which is probably as well when you so easily dismiss his advice. O' I do not dismiss Tom's advice, not at all. Tom is one of a few contributors to this group who's advice is invaluable. And to be quite frank with you, I doubt Tom has been even remotely offended by my question! Stuart |
Application Problem
Tom......you were right! your sugestions done the trick. However I am still
a little unsure why. The problem arrises when another sub sets the criteria to LookIn:- values and I need to change that criteria to LookIn Formulas to get the following line to work Sheets("VS").Columns("B").Find(what:=rng).Offset(0 , 8) =Sheets("VS").Columns("B").Find(what:=rng).Offset( 0, 8) + rng.Offset(0, 4) However I am not looking in formulas, a typical value of rng would be "WS342" and the search would be for a cell containing the same, No Fomulae on the page! Tom Ogilvy wrote in message ... Hopefully you will get a more meaningful answer from someone more knowledgeable. -- Regards, Tom Ogilvy Stuart wrote in message ... I will give that a go next time the problem occurs, however, I doubt that is the answer, as it performs a very simillar find with the same settings on a very simillar piece of date 10 lines before the problem line! And why won't it work properly again until the Application is reStarted, simply reStarting the workbook makes no differance! Tom Ogilvy wrote in message ... Find has several persistent values. You are only setting the search target, so some previous use of find could set one of the other values so that the find is not made. xlwhole vice xlpart, xlformulas vice xlvalues for example can affect whether the target is found. If it is a date, then it can get even more complicated. -- Regards, Tom Ogilvy |
Application Problem
Given your description, I would think xlValues would work, but since it
doesn't, I think one would have to play with the sheet to figure it out. (however it sounds more like a problem with xlpart vice xlwhole and possibly a blank or char(160) in the string). -- Regards, Tom Ogilvy Stuart wrote in message ... Tom......you were right! your sugestions done the trick. However I am still a little unsure why. The problem arrises when another sub sets the criteria to LookIn:- values and I need to change that criteria to LookIn Formulas to get the following line to work Sheets("VS").Columns("B").Find(what:=rng).Offset(0 , 8) =Sheets("VS").Columns("B").Find(what:=rng).Offset( 0, 8) + rng.Offset(0, 4) However I am not looking in formulas, a typical value of rng would be "WS342" and the search would be for a cell containing the same, No Fomulae on the page! Tom Ogilvy wrote in message ... Hopefully you will get a more meaningful answer from someone more knowledgeable. -- Regards, Tom Ogilvy Stuart wrote in message ... I will give that a go next time the problem occurs, however, I doubt that is the answer, as it performs a very simillar find with the same settings on a very simillar piece of date 10 lines before the problem line! And why won't it work properly again until the Application is reStarted, simply reStarting the workbook makes no differance! Tom Ogilvy wrote in message ... Find has several persistent values. You are only setting the search target, so some previous use of find could set one of the other values so that the find is not made. xlwhole vice xlpart, xlformulas vice xlvalues for example can affect whether the target is found. If it is a date, then it can get even more complicated. -- Regards, Tom Ogilvy |
All times are GMT +1. The time now is 03:37 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com