Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advice on code
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advice on code
On May 19, 10:31*am, Les 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Advice on code
Thanks a million JW.
Your help is much appreciated. -- Les "JW" wrote: On May 19, 10:31 am, Les 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Advice on code | Excel Programming | |||
Need some advice on the following code | Excel Programming | |||
Little more advice on this code | Excel Discussion (Misc queries) | |||
Little more advice on this code | Excel Programming | |||
Code advice please... | Excel Programming |