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!!! |
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 |
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!!! |
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!!! |
All times are GMT +1. The time now is 07:48 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com