Results 1 to 4 of 4

Thread: Find and Replace based on Selection only

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #1
    VBAX Newbie
    Joined
    Apr 2019
    Posts
    3
    Location

    Find and Replace based on Selection only

    Trying to modify this code to select a range and then have find & replace. Since the words may appear in other areas of the worksheet I only want the selected range.
    ie. the attached sheet - If I select G:G in the Input Box, B:B will not change


    Sub FindReplaceAll()
    
    Dim sht As Worksheet
    Dim fnd As Variant
    Dim rplc As Variant
    Dim x As Long
    Dim rng As Range
    
    
    Set rng = Application.InputBox(Prompt:="Please select the range with the mouse", Title:="Selection required", Type:=8)
    
    
    fndList = Array("Unsubstantiated", "Substantiated", "Insufficient information to substantiate")
    rplcList = Array("Invalid", "Valid", "Insufficient")
    
    
    rng.Cells.Replace What:=fndList(x), Replacement:=rplcList(x), _
              LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
              SearchFormat:=False, ReplaceFormat:=False
              
           
    
    
    End Sub
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •