Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying to limit .Find to a specific range
I am trying to find occurances of value within a range of cells.
Could someone help me with this code? tia For Each rngMyCell In rngMyRange With Cells Set rngMyCell = .Find(What:=strEdit_Find, After:=ActiveCell,_ LookIn:=xlValues, LookAt:=xlPart, _ SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False) If Not rngMyCell Is Nothing Then strFirstAddress = rngMyCell.Address Do strData = rngMyCell.Formula lCountValueReplaces = lCountValueReplaces + 1 Set rngMyCell = .FindNext(rngMyCell) Loop While Not rngMyCell Is Nothing And ngMyCell.Address < strFirstAddress End If End With Next |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying to limit .Find to a specific range
Hi:
Instead of: With Cells use: With Range("A1:Z100") 'or whatever Regards, Vasant. wrote in message ... I am trying to find occurances of value within a range of cells. Could someone help me with this code? tia For Each rngMyCell In rngMyRange With Cells Set rngMyCell = .Find(What:=strEdit_Find, After:=ActiveCell,_ LookIn:=xlValues, LookAt:=xlPart, _ SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False) If Not rngMyCell Is Nothing Then strFirstAddress = rngMyCell.Address Do strData = rngMyCell.Formula lCountValueReplaces = lCountValueReplaces + 1 Set rngMyCell = .FindNext(rngMyCell) Loop While Not rngMyCell Is Nothing And ngMyCell.Address < strFirstAddress End If End With Next |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying to limit .Find to a specific range
Bill,
Just change With Cells to With Range("A1:A10") or whatever range you want to search. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com wrote in message ... I am trying to find occurances of value within a range of cells. Could someone help me with this code? tia For Each rngMyCell In rngMyRange With Cells Set rngMyCell = .Find(What:=strEdit_Find, After:=ActiveCell,_ LookIn:=xlValues, LookAt:=xlPart, _ SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False) If Not rngMyCell Is Nothing Then strFirstAddress = rngMyCell.Address Do strData = rngMyCell.Formula lCountValueReplaces = lCountValueReplaces + 1 Set rngMyCell = .FindNext(rngMyCell) Loop While Not rngMyCell Is Nothing And ngMyCell.Address < strFirstAddress End If End With Next |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying to limit .Find to a specific range
Dim rngMyRange as Range
Dim rngMyCell as Range Dim strFirstAddress as String Dim strEdit_Find as String Dim strData as String Dim lCountValueReplaces as Long With rngMyRange Set rngMyCell = .Find(What:=strEdit_Find, _ After:=rngMyRange(rng.MyRange.count), _ LookIn:=xlValues, LookAt:=xlPart, _ SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False) If Not rngMyCell Is Nothing Then strFirstAddress = rngMyCell.Address Do strData = strData & rngMyCell.Formula & vbNewLine lCountValueReplaces = lCountValueReplaces + 1 Set rngMyCell = .FindNext(rngMyCell) Loop While Not rngMyCell Is Nothing And _ rngMyCell.Address < strFirstAddress End If End With msgbox strData Your not doing any replacing, so I am not sure what you want out of lcountValueReplaces It will count the number cells found containing strEdit_Find -- Regards, Tom Ogilvy wrote in message ... I am trying to find occurances of value within a range of cells. Could someone help me with this code? tia For Each rngMyCell In rngMyRange With Cells Set rngMyCell = .Find(What:=strEdit_Find, After:=ActiveCell,_ LookIn:=xlValues, LookAt:=xlPart, _ SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False) If Not rngMyCell Is Nothing Then strFirstAddress = rngMyCell.Address Do strData = rngMyCell.Formula lCountValueReplaces = lCountValueReplaces + 1 Set rngMyCell = .FindNext(rngMyCell) Loop While Not rngMyCell Is Nothing And ngMyCell.Address < strFirstAddress End If End With Next |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying to limit .Find to a specific range
Thanks, that worked perfectly
On Sun, 2 Nov 2003 15:42:14 -0600, "Chip Pearson" wrote: Bill, Just change With Cells to With Range("A1:A10") or whatever range you want to search. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying to limit .Find to a specific range
If that is all you changed, you are wasting a lot of time in your code. You
are also changing your loop variable within the loop which is not recommended, but you don't need the loop anyway. -- Regards, Tom Ogilvy wrote in message ... Thanks, that worked perfectly On Sun, 2 Nov 2003 15:42:14 -0600, "Chip Pearson" wrote: Bill, Just change With Cells to With Range("A1:A10") or whatever range you want to search. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find & Replace - Limit search to a specific column | Excel Worksheet Functions | |||
Limit Printing on specific worksheets in Excel | Excel Discussion (Misc queries) | |||
Find the bottom cell that contains a specific val in a range? | Excel Worksheet Functions | |||
find sum if one col = ? within specific date range | Excel Discussion (Misc queries) | |||
find numbers in a range that add to a specific value | Excel Discussion (Misc queries) |