Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Oject value changing in a loop
The proc below is a general search called by other procs.
The IAllRtesOrOne input var determines if one or more sheets will potentially be searched. At issue are code lines A B C or A D E as described below, especially line B. I am still learning about objects, but ADE works as planned with one or more than one sheets being searched. ABC works when only one sheet is searched, but line B gets a run time object error when looping to the second sheet. I don't understand the problem. Help please. Thanks, Neal Z. Sub zString_FindGeneral(ByRef IFindThis As String, _ ByRef IAllRtesOrOne As String, _ ByRef IFmRow As Long, ByRef IFmCol As Integer, ByRef IToRow As Long, ByRef IToCol As Integer, _ ByRef OErrMsg As String, _ ByRef OFoundQty As Integer, ByRef OFoundAry As Variant) ' Fill the OFoundAry array with the results of a string search; ' Calling macro determines if one sheet or more will be searched. ' Array column 1= worksheet name, 2= row number, 3= column number ' OFoundQty is the number of cells found containing the IFindThis string. Dim RteQty As Integer, Route As String Dim RteIx As Integer ' index Dim RteNameAry(1 To 26) As String Dim RteCells As Object, FirstAddress As String, FoundCell As Object Const AryRteCol = 1 ' Columns in the two dimension OFoundAry Const AryRowCol = 2 Const AryColCol = 3 OFoundQty = 0 OErrMsg = "" Application.ScreenUpdating = False If LCase(IAllRtesOrOne) = "all" Then 'make array of selected sheet names Call zRte_NameAry_Make(Yes, No, "", RteQty, RteNameAry, OErrMsg) If OErrMsg < "" Then Exit Sub Else RteQty = 1 RteNameAry(1) = activesheet.name End If Set FoundCell = .Find(IFindThis, LookIn:=xlFormulas) For RteIx = 1 To RteQty 'LINES A B C WORK WHEN SEARCHING ONE WORKSHEET (RteQty=1), ' lines D E were commented out. 'line B errors out with application or object defined error when RteQty ' is 1, i.e. more than 1 sheet is supposed to be searched. Route = RteNameAry(RteIx) 'LINE A 'Set RteCells = Worksheets(Route).Range(Cells(IFmRow, IFmCol),Cells(IToRow, IToCol)) 'LINE B 'With RteCells 'LINE C 'Lines A, D, E work when searching one or multiple worksheets ' with lines B C commented out. Worksheets(Route).Activate 'LINE D With Range(Cells(IFmRow, IFmCol), Cells(IToRow, IToCol)) 'LINE E If Not FoundCell Is Nothing Then FirstAddress = FoundCell.Address OFoundQty = OFoundQty + 1 OFoundAry(OFoundQty, AryRteCol) = Route OFoundAry(OFoundQty, AryRowCol) = FoundCell.Row OFoundAry(OFoundQty, AryColCol) = FoundCell.Column Do Set FoundCell = .FindNext(FoundCell) If Not FoundCell Is Nothing And FoundCell.Address < FirstAddress Then If OFoundQty < UBound(OFoundAry, 1) Then OFoundQty = OFoundQty + 1 OFoundAry(OFoundQty, AryRteCol) = Route OFoundAry(OFoundQty, AryRowCol) = FoundCell.Row OFoundAry(OFoundQty, AryColCol) = FoundCell.Column Else MsgBox "Program limit of " & UBound(OFoundAry, 1) & _ "found cells has been reached." Exit Do End If End If Loop Until Not FoundCell Is Nothing And FoundCell.Address = FirstAddress Else 'IFindThis string is not found the first time End If End With If OFoundQty = UBound(OFoundAry, 1) Then Exit For Next RteIx End Sub -- Neal Z |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Oject value changing in a loop
'Set RteCells = Worksheets(Route).Range(Cells(IFmRow, IFmCol),Cells(IToRow,
IToCol)) should be With Worksheets(Route) set RteCells = .Range(.Cells(IFmRow,IFmCol), _ .Cells(IToRow,IToCol)) End With -- Regards, Tom Ogilvy "Neal Zimm" wrote: The proc below is a general search called by other procs. The IAllRtesOrOne input var determines if one or more sheets will potentially be searched. At issue are code lines A B C or A D E as described below, especially line B. I am still learning about objects, but ADE works as planned with one or more than one sheets being searched. ABC works when only one sheet is searched, but line B gets a run time object error when looping to the second sheet. I don't understand the problem. Help please. Thanks, Neal Z. Sub zString_FindGeneral(ByRef IFindThis As String, _ ByRef IAllRtesOrOne As String, _ ByRef IFmRow As Long, ByRef IFmCol As Integer, ByRef IToRow As Long, ByRef IToCol As Integer, _ ByRef OErrMsg As String, _ ByRef OFoundQty As Integer, ByRef OFoundAry As Variant) ' Fill the OFoundAry array with the results of a string search; ' Calling macro determines if one sheet or more will be searched. ' Array column 1= worksheet name, 2= row number, 3= column number ' OFoundQty is the number of cells found containing the IFindThis string. Dim RteQty As Integer, Route As String Dim RteIx As Integer ' index Dim RteNameAry(1 To 26) As String Dim RteCells As Object, FirstAddress As String, FoundCell As Object Const AryRteCol = 1 ' Columns in the two dimension OFoundAry Const AryRowCol = 2 Const AryColCol = 3 OFoundQty = 0 OErrMsg = "" Application.ScreenUpdating = False If LCase(IAllRtesOrOne) = "all" Then 'make array of selected sheet names Call zRte_NameAry_Make(Yes, No, "", RteQty, RteNameAry, OErrMsg) If OErrMsg < "" Then Exit Sub Else RteQty = 1 RteNameAry(1) = activesheet.name End If Set FoundCell = .Find(IFindThis, LookIn:=xlFormulas) For RteIx = 1 To RteQty 'LINES A B C WORK WHEN SEARCHING ONE WORKSHEET (RteQty=1), ' lines D E were commented out. 'line B errors out with application or object defined error when RteQty ' is 1, i.e. more than 1 sheet is supposed to be searched. Route = RteNameAry(RteIx) 'LINE A 'Set RteCells = Worksheets(Route).Range(Cells(IFmRow, IFmCol),Cells(IToRow, IToCol)) 'LINE B 'With RteCells 'LINE C 'Lines A, D, E work when searching one or multiple worksheets ' with lines B C commented out. Worksheets(Route).Activate 'LINE D With Range(Cells(IFmRow, IFmCol), Cells(IToRow, IToCol)) 'LINE E If Not FoundCell Is Nothing Then FirstAddress = FoundCell.Address OFoundQty = OFoundQty + 1 OFoundAry(OFoundQty, AryRteCol) = Route OFoundAry(OFoundQty, AryRowCol) = FoundCell.Row OFoundAry(OFoundQty, AryColCol) = FoundCell.Column Do Set FoundCell = .FindNext(FoundCell) If Not FoundCell Is Nothing And FoundCell.Address < FirstAddress Then If OFoundQty < UBound(OFoundAry, 1) Then OFoundQty = OFoundQty + 1 OFoundAry(OFoundQty, AryRteCol) = Route OFoundAry(OFoundQty, AryRowCol) = FoundCell.Row OFoundAry(OFoundQty, AryColCol) = FoundCell.Column Else MsgBox "Program limit of " & UBound(OFoundAry, 1) & _ "found cells has been reached." Exit Do End If End If Loop Until Not FoundCell Is Nothing And FoundCell.Address = FirstAddress Else 'IFindThis string is not found the first time End If End With If OFoundQty = UBound(OFoundAry, 1) Then Exit For Next RteIx End Sub -- Neal Z |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Oject value changing in a loop
Tom -
Aha! The fog lifts.... So when you're within a 'With' construct, the item having the suffix to the "with suffix" phrase must be proceeded by the period. thanks much. -- Neal Z "Tom Ogilvy" wrote: 'Set RteCells = Worksheets(Route).Range(Cells(IFmRow, IFmCol),Cells(IToRow, IToCol)) should be With Worksheets(Route) set RteCells = .Range(.Cells(IFmRow,IFmCol), _ .Cells(IToRow,IToCol)) End With -- Regards, Tom Ogilvy "Neal Zimm" wrote: The proc below is a general search called by other procs. The IAllRtesOrOne input var determines if one or more sheets will potentially be searched. At issue are code lines A B C or A D E as described below, especially line B. I am still learning about objects, but ADE works as planned with one or more than one sheets being searched. ABC works when only one sheet is searched, but line B gets a run time object error when looping to the second sheet. I don't understand the problem. Help please. Thanks, Neal Z. Sub zString_FindGeneral(ByRef IFindThis As String, _ ByRef IAllRtesOrOne As String, _ ByRef IFmRow As Long, ByRef IFmCol As Integer, ByRef IToRow As Long, ByRef IToCol As Integer, _ ByRef OErrMsg As String, _ ByRef OFoundQty As Integer, ByRef OFoundAry As Variant) ' Fill the OFoundAry array with the results of a string search; ' Calling macro determines if one sheet or more will be searched. ' Array column 1= worksheet name, 2= row number, 3= column number ' OFoundQty is the number of cells found containing the IFindThis string. Dim RteQty As Integer, Route As String Dim RteIx As Integer ' index Dim RteNameAry(1 To 26) As String Dim RteCells As Object, FirstAddress As String, FoundCell As Object Const AryRteCol = 1 ' Columns in the two dimension OFoundAry Const AryRowCol = 2 Const AryColCol = 3 OFoundQty = 0 OErrMsg = "" Application.ScreenUpdating = False If LCase(IAllRtesOrOne) = "all" Then 'make array of selected sheet names Call zRte_NameAry_Make(Yes, No, "", RteQty, RteNameAry, OErrMsg) If OErrMsg < "" Then Exit Sub Else RteQty = 1 RteNameAry(1) = activesheet.name End If Set FoundCell = .Find(IFindThis, LookIn:=xlFormulas) For RteIx = 1 To RteQty 'LINES A B C WORK WHEN SEARCHING ONE WORKSHEET (RteQty=1), ' lines D E were commented out. 'line B errors out with application or object defined error when RteQty ' is 1, i.e. more than 1 sheet is supposed to be searched. Route = RteNameAry(RteIx) 'LINE A 'Set RteCells = Worksheets(Route).Range(Cells(IFmRow, IFmCol),Cells(IToRow, IToCol)) 'LINE B 'With RteCells 'LINE C 'Lines A, D, E work when searching one or multiple worksheets ' with lines B C commented out. Worksheets(Route).Activate 'LINE D With Range(Cells(IFmRow, IFmCol), Cells(IToRow, IToCol)) 'LINE E If Not FoundCell Is Nothing Then FirstAddress = FoundCell.Address OFoundQty = OFoundQty + 1 OFoundAry(OFoundQty, AryRteCol) = Route OFoundAry(OFoundQty, AryRowCol) = FoundCell.Row OFoundAry(OFoundQty, AryColCol) = FoundCell.Column Do Set FoundCell = .FindNext(FoundCell) If Not FoundCell Is Nothing And FoundCell.Address < FirstAddress Then If OFoundQty < UBound(OFoundAry, 1) Then OFoundQty = OFoundQty + 1 OFoundAry(OFoundQty, AryRteCol) = Route OFoundAry(OFoundQty, AryRowCol) = FoundCell.Row OFoundAry(OFoundQty, AryColCol) = FoundCell.Column Else MsgBox "Program limit of " & UBound(OFoundAry, 1) & _ "found cells has been reached." Exit Do End If End If Loop Until Not FoundCell Is Nothing And FoundCell.Address = FirstAddress Else 'IFindThis string is not found the first time End If End With If OFoundQty = UBound(OFoundAry, 1) Then Exit For Next RteIx End Sub -- Neal Z |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Looping SQL query w/changing parameters in each loop not working | Excel Programming | |||
Advancing outer Loop Based on criteria of inner loop | Excel Programming | |||
Changing range name in loop | Excel Programming | |||
Excel VBA - changing variables during a loop | Excel Programming | |||
Loop for changing cell formatting | Excel Programming |