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.

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



"turen" wrote:

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.

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.

"Barb Reinhardt" wrote:

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



"turen" wrote:

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.

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

--
Regards,

Halim



"turen" wrote:

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.

"Barb Reinhardt" wrote:

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



"turen" wrote:

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.

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

"turen" wrote in message
...
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.

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

Turen

"Tim Williams" wrote:

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

"turen" wrote in message
...
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.