Search and return value

  • Thread starter Thread starter index
  • Start date Start date
I

index

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
 
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 said:
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


------------------------------------------------



~~Now Available: Financial Statements.xls, a step by step guide to
Click to expand...
creating financial statements
 
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.
 
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
 
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
 
Back
Top