Hello all, i have to loop down a sheet in column "L" and for each cell i need
to calculate "=(J12-H12)/30.5". Is it better/Quicker to do the calculation in
VBA and then insert the result in applicable row in in column "L", or is it
better to loop down and put a calculation in each cell ?

--
Les

On May 19, 10:31*am, Les <L...@discussions.microsoft.com> wrote:
> Hello all, i have to loop down a sheet in column "L" and for each cell i need
> to calculate "=(J12-H12)/30.5". Is it better/Quicker to do the calculation in
> VBA and then insert the result in applicable row in in column "L", or is it
> better to loop down and put a calculation in each cell ?
>
> --
> Les

The speed of the calculation would depend on the amount of data in
your worksheet. If you need the values in L to be dynamic once they
are entered, then you will need to leave the formula intact. If you
don't need them to be dynamic, then you can just place the calculated
value in the cell.

This will place the formula in L12 and autofill down to the last used
row in column A/

Sub Example4()
Dim lRow As Long
lRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("L12").Formula = "=(J12-H12)/30.5"
Range("L12").AutoFill Destination:=Range("L12:L" & lRow)
End Sub

This is one way of looping through column L to the last used row in
column A and place the calculated value.

Sub Example5()
Dim lRow As Long, i As Long
lRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 12 To lRow
Cells(i, "L").Value = _
(Range("J" & i).Value - Range("H" & i).Value) / 30.5
Next i
End Sub

Thanks a million JW.

Your help is much appreciated.
--
Les


"JW" wrote:

> On May 19, 10:31 am, Les <L...@discussions.microsoft.com> wrote:
> > Hello all, i have to loop down a sheet in column "L" and for each cell i need
> > to calculate "=(J12-H12)/30.5". Is it better/Quicker to do the calculation in
> > VBA and then insert the result in applicable row in in column "L", or is it
> > better to loop down and put a calculation in each cell ?
> >
> > --
> > Les
>
> The speed of the calculation would depend on the amount of data in
> your worksheet. If you need the values in L to be dynamic once they
> are entered, then you will need to leave the formula intact. If you
> don't need them to be dynamic, then you can just place the calculated
> value in the cell.
>
> This will place the formula in L12 and autofill down to the last used
> row in column A/
>
> Sub Example4()
> Dim lRow As Long
> lRow = Cells(Rows.Count, "A").End(xlUp).Row
> Range("L12").Formula = "=(J12-H12)/30.5"
> Range("L12").AutoFill Destination:=Range("L12:L" & lRow)
> End Sub
>
> This is one way of looping through column L to the last used row in
> column A and place the calculated value.
>
> Sub Example5()
> Dim lRow As Long, i As Long
> lRow = Cells(Rows.Count, "A").End(xlUp).Row
> For i = 12 To lRow
> Cells(i, "L").Value = _
> (Range("J" & i).Value - Range("H" & i).Value) / 30.5
> Next i
> End Sub
>