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 Range, rng As Range, s As String, c As String
Dim LR As Long
With Worksheets("Report")
LR = .Range("G" & .Rows.Count).End(xlUp).Row
Set r = .Range("G5:G" & LR)
s = InputBox("What Value to find and change in Column G ?")
c = InputBox("change with Value")
.Range("G5:G" & LR).AutoFilter Field:=1, Criteria1:=s
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(0, 1) = Round(c * 0.88, 2) 'this changes Column H
rng.Offset(0, 2) = Round(c * 0.12, 2) 'this changes Column I
rng.Offset(0, 3) = c '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
Bookmarks