how to change multiple value instead of one value at a time?

[rrosa1]

hi
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

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

any help is greatly appreciated
hear is the sample WB thanks

[Oorang]

You can assign the same value to multiple cells like this:

Range("A1:A5").Value = "MyValue"

You can assign multiple values to multiple cells in a row by using a two-dimensional array:

Sub Example1()
    Dim myHardCodedData() As String
    ReDim myHardCodedData(1 To 2, 1 To 2) As String
    myHardCodedData(1, 1) = "A"
    myHardCodedData(1, 2) = "B"
    myHardCodedData(2, 1) = "C"
    myHardCodedData(2, 2) = "D"
    Range("A1:B2").Value = myHardCodedData
End Sub

If all the data is going into only one row then you can do a one dimensional array:

Sub Example2()
    Dim myHardCodedData() As String
    ReDim myHardCodedData(1 To 4) As String
    myHardCodedData(1) = "A"
    myHardCodedData(2) = "B"
    myHardCodedData(3) = "C"
    myHardCodedData(4) = "D"
    Range("A1:D1").Value = myHardCodedData
End Sub

One common use of the 1 dimensional array trick is to apply headers:

Sub Example3()
    Range("A1:D1").Value = Array("ID", "Customer", "Date", "Amount")
End Sub

[Bob Phillips]

You could filter on multiple values, but then you would need iterate the visible cells to determine what changes to what.

[rrosa1]

hi xld and oorang
actualy 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
i hope i explain my problem

[rrosa1]

hi
i found this code on net which does change the value of column G
but i add column H in to the original code and it change whole column H
to the value so how can i code to replace the value of column H with ref to the column G as my original code do
any help is greatly appreciated.

Sub ptester()
Dim Nv As Long
Nv = 40.99
Results = Array("40", Nv, "57.98", Nv, "57.94", Nv)
For p = 0 To UBound(Results, 1) Step 2
    Columns("G:G").Replace What:=Results(p), Replacement:=Results(p + 1), LookAt:=xlWhole, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
    'p.Offset(0, 0) = Nv   'this changes Column G visible cells
    Columns("H:H") = Round(Nv * 0.88, 2)
    'p.Offset(0, 1) = Round(Nv * 0.88, 2)    'this changes Column H
    'p.Offset(0, 2) = Round(Nv * 0.12, 2)    'this changes Column I
    'p.Offset(0, 3) = Nv    'this changes Column J
    'p.Offset(0, -1) = Nv    'this changes Column f
    '.AutoFilterMode = False
        Next
End Sub

[Bob Phillips]

Sub ptester()
    Dim Nv As Double
    Dim Results As Variant
    Dim p As Long
Nv = 40.99
    Results = Array(40, 57.98, 57.94)
For p = 0 To UBound(Results, 1)
        Columns("G:G").Replace What:=Results(p), Replacement:=Nv, LookAt:=xlWhole, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        Columns("H:H") = Round(Nv * 0.88, 2)
    Next
End Sub

[rrosa1]

hi Xld
it does the thing in column H but the problem is it change the value of column H with = Round(Nv * 0.88, 2)
but not corespoding value with column G cell
is there anyway we can change the code to do change value in G cell and in same row it change the value of H column cell with corespondent to G cell.
thanks for help