PDA

View Full Version : how to change multiple value instead of one value at a time?



rrosa1
07-24-2010, 11:37 AM
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
07-24-2010, 12:35 PM
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
07-24-2010, 12:35 PM
You could filter on multiple values, but then you would need iterate the visible cells to determine what changes to what.

rrosa1
07-24-2010, 12:46 PM
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
07-25-2010, 05:58 PM
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
07-26-2010, 01:11 AM
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
07-26-2010, 06:28 AM
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