Change data value in a range of cells

[Guest]

I want to convert data value in a range of cells according to a specified
rule. For example, To divide the value in each cell by the value in the
first cell (row) in each column and multiply by 100.

1 2 100 100
2 3 200 150
3 4 To 300 200
4 5 400 250
5 6 500 300

If I enter formula for each cell one by one, it will be a very laboring
process. It seems there should be a simpler way to do this. Can someone
please help me? Thank you very much.

 

[Guest]

Try entering your formula in the top cell of the range. You should then
see a little black box in the lower right hand corner of the cell. Click on
that and drag down as far as necessary.

HTH,
Barb Reinhardt

 

[Guest]

Thanks for the reply. But this will only give me the same value as that in
the top cell. It seems I need to write a VB Script to select a range of
cells and then divide the value in each cell by certain value. hope someone
can tell me how to do that.

 

[Guest]

I hope my post will give your correct query:

Sub test()
Dim r, c
For c = 1 To 2
For r = 1 To 5
If c = 1 Then
Cells(r, c) = r
Cells(r, c).Offset(0, 2) = _
Cells(r, c) * 100
ElseIf c = 2 Then
Cells(r, c) = r + 1
Cells(r, c).Offset(0, 2) = _
Cells(r, c) * 50
End If
Next r
Next c
End Sub

 

[Tim Williams]

Select your dataset and run this

'*****************
Sub Tester()

Dim col As Range, c As Range
Dim v

For Each col In Selection.Columns
v = col.Cells(1).Value
For Each c In col.Cells
c.Value = (c.Value / v) * 100
Next c
Next col

End Sub
'*****************

Tim

 

[Guest]

Thank you very much, Tim. It works perfectly. Thank you all for taking time
to reply.

Turen