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

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

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

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

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