+ Reply to Thread
Results 1 to 3 of 3

how to filter multiple value and replace with one value

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    02-12-2010
    Location
    india
    MS-Off Ver
    Excel 2007
    Posts
    134

    how to filter multiple value and replace with one value

    hi
    how to filter multiple value and replace with one value?
    i have following code which change value but one at a time is that possible to in put multiple value to change ?
    my code is like this

    PHP Code: 
    Sub ChangeValue_4() 
        
    Dim r As Rangerng As RangeAs StringAs String 
        Dim LR 
    As Long 
        With Worksheets
    ("Report"
            
    LR = .Range("G" & .Rows.Count).End(xlUp).Row 
            Set r 
    = .Range("G5:G" LR
            
    InputBox("What Value to find and change in Column G ?"
            
    InputBox("change with Value"
            .
    Range("G5:G" LR).AutoFilter Field:=1Criteria1:=
            On Error Resume Next 
            Set rng 
    = .Range("G6:G" LR).SpecialCells(xlCellTypeVisible'if no items found
            On Error Goto 0 
            If rng Is Nothing Then 
                MsgBox "No records found" 
                .AutoFilterMode = False 
                Exit Sub 
            End If 
            rng.Offset(0, 0) = c '
    this changes Column G visible cells
            rng
    .Offset(01) = Round(0.882'this changes Column H
            rng.Offset(0, 2) = Round(c * 0.12, 2) '
    this changes Column I
            rng
    .Offset(03) = 'this changes Column J
            rng.Offset(0, -1) = c '
    this changes Column f
            
    .AutoFilterMode False 
        End With 
         
    End Sub 
    in this code i need to filter the value in column G as suppose
    50.81,49.85,42.65,65.89 (this value input by user)

    and change to like say 45.99 (this value also input by user ) to all four value in column G and do the rest so i don't need to run the macro more time and there by save some time.
    i hope i explain my problem
    here is the sample wb
    Attached Files Attached Files
    Last edited by jay11; 07-26-2010 at 10:04 AM.

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: how to filter multiple value and replace with one value

    Try this:
    Option Explicit
    
    Sub ChangeValue_4()
    Dim r As Range, RNG As Range, s As Double, c As Double
    Dim LR As Long
    Application.ScreenUpdating = False
        
        With Worksheets("Report")
            .AutoFilterMode = False
            LR = .Range("G" & .Rows.Count).End(xlUp).Row
            Set r = .Range("G5:G" & LR)
            c = Application.InputBox("Value to be inserted?", "New Value", Type:=2)
            If c = 0 Then GoTo ExitHandler
            Do
                Set RNG = Nothing
                s = 0
                On Error Resume Next
                s = Application.InputBox("What Value to find and change in Column G ?", "Search Value", Type:=2)
                If s = 0 Then GoTo ExitHandler
                r.AutoFilter Field:=1, Criteria1:=Format(s, "0.00")
                Set RNG = .Range("G6:G" & LR).SpecialCells(xlCellTypeVisible)    'if no items found
                If RNG Is Nothing Then
                    MsgBox "No records found with value " & s
                Else
                    RNG.Value = c   'this changes Column G visible cells
                    RNG.Offset(, 1).Value = Round(c * 0.88, 2)    'this changes Column H
                    RNG.Offset(, 2).Value = Round(c * 0.12, 2)    'this changes Column I
                    RNG.Offset(, 3).Value = c    'this changes Column J
                    RNG.Offset(, -1).Value = c    'this changes Column f
                    .AutoFilterMode = False
                End If
            Loop
        End With
    
    ExitHandler:
    Set r = Nothing
    Application.ScreenUpdating = True
    End Sub
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Forum Contributor
    Join Date
    02-12-2010
    Location
    india
    MS-Off Ver
    Excel 2007
    Posts
    134

    Re: how to filter multiple value and replace with one value

    thanks JB
    it work great

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1