Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a Macro to find a value in a range and activate it
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a Macro to find a value in a range and activate it
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] Travis wrote: 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a Macro to find a value in a range and activate it
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 ------------------- -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a Macro to find a value in a range and activate it
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] Travis wrote: 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? . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a Macro to find a value in a range and activate it
Hi Travis,
travis wrote: 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 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] |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to find value and then copy associated range | Excel Discussion (Misc queries) | |||
Activate cell at end of range | Setting up and Configuration of Excel | |||
use of range and activate | Excel Programming | |||
range.activate | Excel Programming | |||
Macro to find the 6th occurrence and move range | Excel Programming |