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
Bookmarks