Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find/REplace from table questions...
I found most of the answers I needed in this post:
http://groups.google.com/group/micro...219aa2adb07dd7 Thanks Barry-Jon for this code: ___________________________ Sub MultiFindReplace() Dim rngReplaceWith As Excel.Range Dim rngSearchArea As Excel.Range Dim lngRepaceCount As Long Set rngReplaceWith = GetUserRange("Please select find/replace values range (two columns)") If Not rngReplaceWith Is Nothing Then 'basic range size validation - a lot more could be done If rngReplaceWith.Columns.Count = 2 Then 'now get the area in which to do the find/replace Set rngSearchArea = GetUserRange("Please select the range in which to find/replace") If Not rngSearchArea Is Nothing Then 'do the search and replace For lngRepaceCount = 1 To rngReplaceWith.Rows.Count rngSearchArea.Replace What:=rngReplaceWith.Cells(lngRepaceCount, 1).Value, _ Replacement:=rngReplaceWith.Cells(lngRepaceCount, 2).Value, _ MatchCase:=False, _ ReplaceFormat:=False Next lngRepaceCount End If Else MsgBox "Invalid find/replace range selected", vbExclamation + vbOKOnly End If End If End Sub Private Function GetUserRange(Prompt As String, Optional Title As String = "Input") As Excel.Range On Error GoTo ErrorHandler Dim retVal As Excel.Range Set retVal = Application.InputBox(Prompt, Title, , , , , , 8) ExitProc: Set GetUserRange = retVal Exit Function ErrorHandler: Set retVal = Nothing Resume ExitProc End Function ________________________________________________ The questions I have: Question 1: How can I make this macro do the "Match Entire Cell Contents" that is available in the Replace menu option? I tried just adding the condition MatchEntireCellContents:=True but that wasn't an acceptable condition. Question 2: Is there a way to reverse the 2 columns in the Find/Replace range? By that I mean, can I make it so that Column 2 in the range is the find value and column 1 is the replace value? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find/REplace from table questions...
Excellent! Thank you for your input. I'm going to give all of that a
try. I haven't progressed much beyond the cutting and pasting of other people's macros, but I've got a book and I'm working on it..... Thanks Dave Dave Peterson wrote: Sometimes, just recording a macro in a test workbook will give you the answer: I recorded this little bit: Cells.Replace What:="a", Replacement:="b", LookAt:=xlWhole, SearchOrder _ :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False Notice the LookAt:=xlWhole parm. You'll want to add it to your .replace line of code. And you can swap columns by changing this line--in fact, I've added the xlwhole stuff, too: rngSearchArea.Replace What:=rngReplaceWith.Cells(lngRepaceCount, 2).Value, _ Replacement:=rngReplaceWith.Cells(lngRepaceCount, 1).Value, _ MatchCase:=False, lookat:=xlWhole, ReplaceFormat:=False Notice the ", 2" and ", 1" have been swapped. They tell excel which column to use. Another option would have been just to swap the "what" and "replacement" keywords. rngSearchArea.Replace _ replacement:=rngReplaceWith.Cells(lngRepaceCount, 1).Value, _ what:=rngReplaceWith.Cells(lngRepaceCount, 2).Value, _ MatchCase:=False, lookat:=xlWhole, ReplaceFormat:=False Now the replacement value comes from column 1 and the "what to replace" value comes from column 2. wrote: I found most of the answers I needed in this post: http://groups.google.com/group/micro...219aa2adb07dd7 Thanks Barry-Jon for this code: ___________________________ Sub MultiFindReplace() Dim rngReplaceWith As Excel.Range Dim rngSearchArea As Excel.Range Dim lngRepaceCount As Long Set rngReplaceWith = GetUserRange("Please select find/replace values range (two columns)") If Not rngReplaceWith Is Nothing Then 'basic range size validation - a lot more could be done If rngReplaceWith.Columns.Count = 2 Then 'now get the area in which to do the find/replace Set rngSearchArea = GetUserRange("Please select the range in which to find/replace") If Not rngSearchArea Is Nothing Then 'do the search and replace For lngRepaceCount = 1 To rngReplaceWith.Rows.Count rngSearchArea.Replace What:=rngReplaceWith.Cells(lngRepaceCount, 1).Value, _ Replacement:=rngReplaceWith.Cells(lngRepaceCount, 2).Value, _ MatchCase:=False, _ ReplaceFormat:=False Next lngRepaceCount End If Else MsgBox "Invalid find/replace range selected", vbExclamation + vbOKOnly End If End If End Sub Private Function GetUserRange(Prompt As String, Optional Title As String = "Input") As Excel.Range On Error GoTo ErrorHandler Dim retVal As Excel.Range Set retVal = Application.InputBox(Prompt, Title, , , , , , 8) ExitProc: Set GetUserRange = retVal Exit Function ErrorHandler: Set retVal = Nothing Resume ExitProc End Function ________________________________________________ The questions I have: Question 1: How can I make this macro do the "Match Entire Cell Contents" that is available in the Replace menu option? I tried just adding the condition MatchEntireCellContents:=True but that wasn't an acceptable condition. Question 2: Is there a way to reverse the 2 columns in the Find/Replace range? By that I mean, can I make it so that Column 2 in the range is the find value and column 1 is the replace value? -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find and Replace - Replace with Blank Space | Excel Discussion (Misc queries) | |||
copy & replace questions | Excel Discussion (Misc queries) | |||
Find/Replace from Table | Excel Programming | |||
find and replace - replace data in rows to separated by commas | Excel Worksheet Functions | |||
Find & Replace questions | Excel Worksheet Functions |