Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search and return value
Is it possible to use a macro to search a sheet to find several value eg, a/b/c/d/e (which could be anywhere on the sheet) and if any of the are found a message is posted in the active cell stating eg "a apples", "b = bananas" with nothing being posted for those not found? Is this,or anything similar, possible? Any help would be much appreciate ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search and return value
It's simple to search for a value use the Find method, for example
Dim cell As Range Set cell = Cells.Find("a") If Not cell Is Nothing Then MsgBox "a found at " & cell.Address What happens if you find a, so you stop and not look for b, or what. Having found a, where do apples come into it? Ditto b and bananas, etc. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "index" wrote in message ... Is it possible to use a macro to search a sheet to find several values eg, a/b/c/d/e (which could be anywhere on the sheet) and if any of them are found a message is posted in the active cell stating eg "a = apples", "b = bananas" with nothing being posted for those not found? Is this,or anything similar, possible? Any help would be much appreciated ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search and return value
One way:
Public Sub FindAnywhere() Dim vLook As Variant Dim vMeans As Variant Dim rFound As Range Dim i As Long Dim sTemp As String vLook = Array("a", "b", "c", "d", "e") vMeans = Array("apple", "banana", "cherry", _ "dog", "elephant") For i = 0 To UBound(vLook) Set rFound = Cells.Find( _ what:=vLook(i), _ LookIn:=xlValues, _ LookAt:=xlWhole, _ MatchCase:=False) If Not rFound Is Nothing Then _ sTemp = sTemp & ", " & vLook(i) & " = " & vMeans(i) Next i ActiveCell.Value = Mid(sTemp, 3) End Sub Note: this won't prevent overwriting one of the values if the active cell contains that value. In article , index wrote: Is it possible to use a macro to search a sheet to find several values eg, a/b/c/d/e (which could be anywhere on the sheet) and if any of them are found a message is posted in the active cell stating eg "a = apples", "b = bananas" with nothing being posted for those not found? Is this,or anything similar, possible? Any help would be much appreciated |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search and return value
Thanks to you both for your help. Using J.E's advice i have achieved what i wanted to do. However, is i possible to split my return value into cells below one another? ie instead of returning A=Apples, B=Bananas in one cell, is it possible to split the answer into A=Apples B=Bananas Thank ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search and return value
One way:
Public Sub FindAnywhere() Dim vLook As Variant Dim vMeans As Variant Dim vOut As Variant Dim rFound As Range Dim i As Long Dim nOut As Long vLook = Array("A", "B", "C", "D", "E") vMeans = Array("Apple", "Banana", "Cherry", _ "Dog", "Elephant") nOut = 0 ReDim vOut(1 To 1) For i = 0 To UBound(vLook) Set rFound = Cells.Find( _ what:=vLook(i), _ LookIn:=xlValues, _ LookAt:=xlWhole, _ MatchCase:=False) If Not rFound Is Nothing Then nOut = nOut + 1 ReDim Preserve vOut(1 To nOut) vOut(nOut) = vLook(i) & "=" & vMeans(i) End If Next i If nOut Then _ ActiveCell.Resize(nOut).Value = _ Application.Transpose(vOut) End Sub In article , index wrote: Thanks to you both for your help. Using J.E's advice i have achieved what i wanted to do. However, is it possible to split my return value into cells below one another? ie instead of returning A=Apples, B=Bananas in one cell, is it possible to split the answer into A=Apples B=Bananas Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
search value in 2 columns return value in another | Excel Worksheet Functions | |||
Search a column for a value and return T or F | Excel Discussion (Misc queries) | |||
search and return adjacent value | Excel Discussion (Misc queries) | |||
Search and return | Excel Worksheet Functions | |||
search for and return more than one row in a sheet | Excel Worksheet Functions |