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" <(E-Mail Removed)> wrote in message
news:A8D0276D-BF65-4059-9BFF-(E-Mail Removed)...
>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" <(E-Mail Removed)> wrote in message
> news:A8D0276D-BF65-4059-9BFF-(E-Mail Removed)...
> >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.
>
>
>