Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find problem
Here's my Macro code (in part). All variables are DIMmed, their type is
indicated by the three letter prefix of the name. strSearchItem = rngOuterCell.Value Sheets("Earnings Balance 2003 Q4 Page 2").Select Range("C3").Select Range(Selection, Selection.End(xlDown)).Select Set c = Selection.Find(What:=strSearchItem, LookIn:=xlValues) If Not c Is Nothing Then lngFoundRow = c.Row GoTo Next1 End If I have a collection of 8 WS, and if "c is Nothing", this process is repeated with the next WS in the collection. (Parenthetically, I have hard coded the WS name into an exact copy of the above code, so I have 8 iteration of it. Messy, and it would be nice if I could execute it in a loop that looks at each member of the Collection in order -- but I don't know how to do it <g). Here is the real problem: When I run this against my first strSearchItem, the sought after target is in Row 3 of the WS, immediately below the two-row header. However when I step through it, in this instance, lngFoundRow is always 4 not 3. WHen I run the code against the next item in the source list, and strSearchItem is not in Row 3, it returns what I consider to be the "correct" value. The only thing I can think of that has been done to the data throughout, is that it was Sorted manually some small time ago. but prior to this. I also tried the search using this: (What:=strSearchItem, After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) And commenting out these: 'Range("C3").Select 'Range(Selection, Selection.End(xlDown)).Select ... same result. Anyone got any ideas why I'm having this problem? -- Dave Temping with Staffmark in Rock Hill, SC |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find problem
Using an Array now for that "Parenthetically" part. All working just fine.
The other problem is still there. -- Dave Temping with Staffmark in Rock Hill, SC "Dave Birley" wrote: Here's my Macro code (in part). All variables are DIMmed, their type is indicated by the three letter prefix of the name. strSearchItem = rngOuterCell.Value Sheets("Earnings Balance 2003 Q4 Page 2").Select Range("C3").Select Range(Selection, Selection.End(xlDown)).Select Set c = Selection.Find(What:=strSearchItem, LookIn:=xlValues) If Not c Is Nothing Then lngFoundRow = c.Row GoTo Next1 End If I have a collection of 8 WS, and if "c is Nothing", this process is repeated with the next WS in the collection. (Parenthetically, I have hard coded the WS name into an exact copy of the above code, so I have 8 iteration of it. Messy, and it would be nice if I could execute it in a loop that looks at each member of the Collection in order -- but I don't know how to do it <g). Here is the real problem: When I run this against my first strSearchItem, the sought after target is in Row 3 of the WS, immediately below the two-row header. However when I step through it, in this instance, lngFoundRow is always 4 not 3. WHen I run the code against the next item in the source list, and strSearchItem is not in Row 3, it returns what I consider to be the "correct" value. The only thing I can think of that has been done to the data throughout, is that it was Sorted manually some small time ago. but prior to this. I also tried the search using this: (What:=strSearchItem, After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) And commenting out these: 'Range("C3").Select 'Range(Selection, Selection.End(xlDown)).Select .. same result. Anyone got any ideas why I'm having this problem? -- Dave Temping with Staffmark in Rock Hill, SC |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find problem
Sub aBC()
Dim fndCell As Range, c As Range Dim shts As Sheets, rng As Range Dim strSearchItem As String Dim ws As Worksheet Set shts = Worksheets(Array("Sheet1", "Sheet5", "Sheet10")) Set rngOuterCell = Worksheets("Sheet2").Range("A2") strSearchItem = rngOuterCell.Value For Each ws In shts Set rng = ws.Range(ws.Range("C3"), _ ws.Range("C3").End(xlDown)) Set c = rng.Find(What:=strSearchItem, _ After:=rng(rng.Count), _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False, _ SearchFormat:=False) If Not c Is Nothing Then lngFoundRow = c.Row Set fndCell = c Exit For End If Next If fndCell Is Nothing Then MsgBox "Not found" Else MsgBox "found at " & fndCell.Address(0, 0, xlA1, True) End If End Sub worked fine for me. -- Regards, Tom Ogilvy "Dave Birley" wrote: Using an Array now for that "Parenthetically" part. All working just fine. The other problem is still there. -- Dave Temping with Staffmark in Rock Hill, SC "Dave Birley" wrote: Here's my Macro code (in part). All variables are DIMmed, their type is indicated by the three letter prefix of the name. strSearchItem = rngOuterCell.Value Sheets("Earnings Balance 2003 Q4 Page 2").Select Range("C3").Select Range(Selection, Selection.End(xlDown)).Select Set c = Selection.Find(What:=strSearchItem, LookIn:=xlValues) If Not c Is Nothing Then lngFoundRow = c.Row GoTo Next1 End If I have a collection of 8 WS, and if "c is Nothing", this process is repeated with the next WS in the collection. (Parenthetically, I have hard coded the WS name into an exact copy of the above code, so I have 8 iteration of it. Messy, and it would be nice if I could execute it in a loop that looks at each member of the Collection in order -- but I don't know how to do it <g). Here is the real problem: When I run this against my first strSearchItem, the sought after target is in Row 3 of the WS, immediately below the two-row header. However when I step through it, in this instance, lngFoundRow is always 4 not 3. WHen I run the code against the next item in the source list, and strSearchItem is not in Row 3, it returns what I consider to be the "correct" value. The only thing I can think of that has been done to the data throughout, is that it was Sorted manually some small time ago. but prior to this. I also tried the search using this: (What:=strSearchItem, After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) And commenting out these: 'Range("C3").Select 'Range(Selection, Selection.End(xlDown)).Select .. same result. Anyone got any ideas why I'm having this problem? -- Dave Temping with Staffmark in Rock Hill, SC |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find problem
Thanks Tom, I'll study that closely and add it to my learning curve. My Array
works about the same as yours, but some of the other code is "more refined", so I will need to learn how to "refine" my stuff. -- Dave Temping with Staffmark in Rock Hill, SC "Tom Ogilvy" wrote: Sub aBC() Dim fndCell As Range, c As Range Dim shts As Sheets, rng As Range Dim strSearchItem As String Dim ws As Worksheet Set shts = Worksheets(Array("Sheet1", "Sheet5", "Sheet10")) Set rngOuterCell = Worksheets("Sheet2").Range("A2") strSearchItem = rngOuterCell.Value For Each ws In shts Set rng = ws.Range(ws.Range("C3"), _ ws.Range("C3").End(xlDown)) Set c = rng.Find(What:=strSearchItem, _ After:=rng(rng.Count), _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False, _ SearchFormat:=False) If Not c Is Nothing Then lngFoundRow = c.Row Set fndCell = c Exit For End If Next If fndCell Is Nothing Then MsgBox "Not found" Else MsgBox "found at " & fndCell.Address(0, 0, xlA1, True) End If End Sub worked fine for me. -- Regards, Tom Ogilvy "Dave Birley" wrote: Using an Array now for that "Parenthetically" part. All working just fine. The other problem is still there. -- Dave Temping with Staffmark in Rock Hill, SC "Dave Birley" wrote: Here's my Macro code (in part). All variables are DIMmed, their type is indicated by the three letter prefix of the name. strSearchItem = rngOuterCell.Value Sheets("Earnings Balance 2003 Q4 Page 2").Select Range("C3").Select Range(Selection, Selection.End(xlDown)).Select Set c = Selection.Find(What:=strSearchItem, LookIn:=xlValues) If Not c Is Nothing Then lngFoundRow = c.Row GoTo Next1 End If I have a collection of 8 WS, and if "c is Nothing", this process is repeated with the next WS in the collection. (Parenthetically, I have hard coded the WS name into an exact copy of the above code, so I have 8 iteration of it. Messy, and it would be nice if I could execute it in a loop that looks at each member of the Collection in order -- but I don't know how to do it <g). Here is the real problem: When I run this against my first strSearchItem, the sought after target is in Row 3 of the WS, immediately below the two-row header. However when I step through it, in this instance, lngFoundRow is always 4 not 3. WHen I run the code against the next item in the source list, and strSearchItem is not in Row 3, it returns what I consider to be the "correct" value. The only thing I can think of that has been done to the data throughout, is that it was Sorted manually some small time ago. but prior to this. I also tried the search using this: (What:=strSearchItem, After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) And commenting out these: 'Range("C3").Select 'Range(Selection, Selection.End(xlDown)).Select .. same result. Anyone got any ideas why I'm having this problem? -- Dave Temping with Staffmark in Rock Hill, SC |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem with 'Find' | Excel Discussion (Misc queries) | |||
Find and Find Next problem | Excel Programming | |||
Please help me find the problem with this | Excel Programming | |||
find problem | Excel Programming | |||
find problem | Excel Programming |