Using a Macro to find a value in a range and activate it

T

Travis

Hi,
I'm trying to write a portion of a macro that will
take the value contained in a specified cell, and look for
another cell in a specified range that has the same
value. I've been working on this for several hours but
haven't had any luck....any suggestions?
 
J

Jake Marx

Hi Travis,

Here's one way to do it:

Public Sub FindMatch(rvFindWhat As Variant, _
rrngTarget As Range, Optional rbMatchCase As _
Boolean = True)
On Error Resume Next
rrngTarget.Find(What:=rvFindWhat, _
MatchCase:=rbMatchCase).Select
On Error GoTo 0
End Sub

Public Sub Test()
FindMatch Range("A1").Value, Range("D1:G10")
End Sub

Basically, you are using the built-in Find tool to find/select the range, if
it exists. If no match is found, nothing happens.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]
 
K

kkknie

I may be misunderstanding, but how about:


Code
-------------------
Sub test()

Dim r As Range
Dim strFind As String

strFind = Range("B13").Value

For Each r In Range("E13:F22")
If r.Value = strFind Then
r.Select
Exit For
End If
Next

End Su
 
T

travis

I think I've left a couple important bits out....the cell
that contains the primary search value is on a different
sheet ("Add-Remove") than the the sheet containing the
range I want to find it in ("Lists"). In addition, once I
find the cell in the range that contains the correct value
(which will be in column a), I want to delete the contents
of the cells in colums B,C,D,E and V in the same row as
the 'found' cell


-----Original Message-----
Hi Travis,

Here's one way to do it:

Public Sub FindMatch(rvFindWhat As Variant, _
rrngTarget As Range, Optional rbMatchCase As _
Boolean = True)
On Error Resume Next
rrngTarget.Find(What:=rvFindWhat, _
MatchCase:=rbMatchCase).Select
On Error GoTo 0
End Sub

Public Sub Test()
FindMatch Range("A1").Value, Range("D1:G10")
End Sub

Basically, you are using the built-in Find tool to find/select the range, if
it exists. If no match is found, nothing happens.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

Hi,
I'm trying to write a portion of a macro that will
take the value contained in a specified cell, and look for
another cell in a specified range that has the same
value. I've been working on this for several hours but
haven't had any luck....any suggestions?
Click to expand...

.
Click to expand...
 
J

Jake Marx

Hi Travis,
I think I've left a couple important bits out....the cell
that contains the primary search value is on a different
sheet ("Add-Remove") than the the sheet containing the
range I want to find it in ("Lists"). In addition, once I
find the cell in the range that contains the correct value
(which will be in column a), I want to delete the contents
of the cells in colums B,C,D,E and V in the same row as
the 'found' cell
Click to expand...

Yes, those are important things to know. <g>

Try this one instead:

Public Sub FindMatch(rvFindWhat As Variant, _
rrngTarget As Range, Optional rbMatchCase As _
Boolean = True)
Dim rng As Range

On Error Resume Next
Set rng = rrngTarget.Find(What:=rvFindWhat, _
MatchCase:=rbMatchCase)
On Error GoTo 0

If Not rng Is Nothing Then
With rng.Parent
.Cells(rng.Row, 2).ClearContents
.Cells(rng.Row, 3).ClearContents
.Cells(rng.Row, 4).ClearContents
.Cells(rng.Row, 5).ClearContents
.Cells(rng.Row, 22).ClearContents
End With
End If
End Sub

Public Sub Test()
With Sheets("List")
FindMatch Sheets("Add-Remove").Range("A1"). _
Value, Application.Intersect(.Range("A:A") _
, .UsedRange)
End With
End Sub


You'll notice that I don't select or activate anything. This will increase
performance and leave the user's current selection alone.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]
 
Top