Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using VB to search for a value within one column
Hello,
How can you search for a value in a sheet while only looking in one column? I use the below code to use the find feature but it searches all cols. ws.Cells.Find(What:="*" & ExcelSearch & "*", After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Activate I wish to search and then display all sheets that have a value in it ( i.e. test) I have done the code for looping through a number of workbooks and then all the worksheets . However what is the best way to check for a value in a sheet. Is it the code above? And if so how do you prevent the search finding the values more than once? I presently compare row numbers , i.e. if the last row where the value was found is greater or equal to the new found value then it must be starting at the top of the page again. Any help would be appreicated!!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using VB to search for a value within one column
dim FoundCell as range
with ws.range("a:a") set foundcell = .cells.find(....,after:=.cells(.cells.count), _ searchdirection:=xlnext, ...) end with And look in VBA's help for .find. You'll find a nice example that uses the address of the first find and uses that to determine when it's starting over. (Row number would work if you were working with a single column, too.) Hcoms wrote: Hello, How can you search for a value in a sheet while only looking in one column? I use the below code to use the find feature but it searches all cols. ws.Cells.Find(What:="*" & ExcelSearch & "*", After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Activate I wish to search and then display all sheets that have a value in it ( i.e. test) I have done the code for looping through a number of workbooks and then all the worksheets . However what is the best way to check for a value in a sheet. Is it the code above? And if so how do you prevent the search finding the values more than once? I presently compare row numbers , i.e. if the last row where the value was found is greater or equal to the new found value then it must be starting at the top of the page again. Any help would be appreicated!!! -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using VB to search for a value within one column
Hcoms
How can you search for a value in a sheet while only looking in one column? I use the below code to use the find feature but it searches all cols. ws.Cells.Find(What:="*" & ExcelSearch & "*", After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Activate ws.Columns(1).Find (etc... just use the Find method on whatever range you want to search and it will search only that range. ws.Cells means every cell on the worksheet. I wish to search and then display all sheets that have a value in it ( i.e. test) I have done the code for looping through a number of workbooks and then all the worksheets . However what is the best way to check for a value in a sheet. Is it the code above? And if so how do you prevent the search finding the values more than once? I presently compare row numbers , i.e. if the last row where the value was found is greater or equal to the new found value then it must be starting at the top of the page again. If you're limiting your search to one column, the rows thing would work. I usually use the Address property as described here http://www.dicks-blog.com/excel/2004...nd_method.html -- Dick Kusleika MVP - Excel Excel Blog - Daily Dose of Excel www.dicks-blog.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using VB to search for a value within one column
One way:
Public Sub Test() Const ExcelSearch As String = "test" Dim wsSheet As Worksheet Dim rFound As Range For Each wsSheet In Worksheets Set rFound = wsSheet.Columns(1).Find( _ What:=ExcelSearch, _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If Not rFound Is Nothing Then Application.GoTo rFound MsgBox "Found " & ExcelSearch & " on sheet " & _ wsSheet.Name End If Next wsSheet End Sub This will only display one msgbox for each sheet that contains "test" in column A. There's no need to compare rows - just move on to the next sheet. In article , "Hcoms" wrote: Hello, How can you search for a value in a sheet while only looking in one column? I use the below code to use the find feature but it searches all cols. ws.Cells.Find(What:="*" & ExcelSearch & "*", After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Activate I wish to search and then display all sheets that have a value in it ( i.e. test) I have done the code for looping through a number of workbooks and then all the worksheets . However what is the best way to check for a value in a sheet. Is it the code above? And if so how do you prevent the search finding the values more than once? I presently compare row numbers , i.e. if the last row where the value was found is greater or equal to the new found value then it must be starting at the top of the page again. Any help would be appreicated!!! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using VB to search for a value within one column
Thank you all for your help! and to Dick for once again helping me out. I am
going to check your website in future first before i ask a question! The only problem i now have is how to close excel. Once my code is finished i close Excel ( or try to!!!) with this code. ( i use late binding by the way) xlapp.Quit xlapp.UserControl = False Set ws = Nothing Set wb = Nothing Set xlapp = Nothing Then when you try running the code ( without closing the vb project) again i either get a type mismatch on the find code or if you try looking at a specific cell or selecting it ( cells(1,1).select ) it errors with 1004 , Method "cells of object" _Global failed. I presume this is something to do with excel not closing completely. Because when you then close the vb program and start it again it is fine! Any Ideas?? Cheers "JE McGimpsey" wrote in message ... One way: Public Sub Test() Const ExcelSearch As String = "test" Dim wsSheet As Worksheet Dim rFound As Range For Each wsSheet In Worksheets Set rFound = wsSheet.Columns(1).Find( _ What:=ExcelSearch, _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If Not rFound Is Nothing Then Application.GoTo rFound MsgBox "Found " & ExcelSearch & " on sheet " & _ wsSheet.Name End If Next wsSheet End Sub This will only display one msgbox for each sheet that contains "test" in column A. There's no need to compare rows - just move on to the next sheet. In article , "Hcoms" wrote: Hello, How can you search for a value in a sheet while only looking in one column? I use the below code to use the find feature but it searches all cols. ws.Cells.Find(What:="*" & ExcelSearch & "*", After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Activate I wish to search and then display all sheets that have a value in it ( i.e. test) I have done the code for looping through a number of workbooks and then all the worksheets . However what is the best way to check for a value in a sheet. Is it the code above? And if so how do you prevent the search finding the values more than once? I presently compare row numbers , i.e. if the last row where the value was found is greater or equal to the new found value then it must be starting at the top of the page again. Any help would be appreicated!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to search in one column, and replace in the other column if fo | Excel Discussion (Misc queries) | |||
Search a column for values, return a value from adj column | Excel Worksheet Functions | |||
LookUp Function with Two Column Search Returning One Column Value | Excel Worksheet Functions | |||
Based on a condition in one column, search for a year in another column, and display data from another column in the same row look | Excel Discussion (Misc queries) | |||
Search one column and return value from next column | Excel Discussion (Misc queries) |