Auto calculating cells

[sparcle]

Hi all,

i have work book with 4 col & no of rows.

i want when i run a macro it will auto calculate cells


here my work book

http://www.2shared.com/file/AT3b2yrn/auto_cal.html


in this work book i want when i run macro it will auto calculate col B & C.

Formula are there.

 

[Hui]

Sparcle


Can't you just copy the existing formulas down?

 

[sparcle]

sir,

this work book contain only few rows but in real it has a lot of rows & i want to auto calculate it.

if any article regarding this please let me know & how to solve this help me.


thank you.

 

[Hui]

But the formulas don't care how many rows there are

Select the cells E6:F6

then double click on the small black square at the bottom right corner of F6

it will copy the formulas down to the end of your data

 

[sparcle]

@ Hui

thank you sir for your help.


if i will record a macro selecting cells E6:F6 it will also do the trick.

what you say sir.

 

[sparcle]

hi,

i have a problem,

in below cell range selection


Selection.AutoFill Destination:=Range("E6:F30")

Range("E6:F30").Select


instead of value 30 in F30 want to put value of a cell J5 which has formula =COUNTA(D5:D100)


i.e. in more explanation if J5 cell value will 10 then it will F10,

if J5 cell value will 20 then it will F20

 

[Hui]

Sparcle


RE: Second last post


I am confused as to why you need a macro


If it is to expand the range to the bottom of the Data Double Clicking as I described above does that easily


If you don't want to see the bottom cells below the data change

E6: =IF(D6="","",IF(D6<F5,F5,D6))

F6: =IF(D6="","",E6+(C6/$C$3))

will hide the cells below the data if it is reduced back by a few rows


RE: Last post


You file has nothing in F30 or J5 or no advanced filter ?

 

[sparcle]

Thank you sir for your valuable answer.

It is true what you say above & its working but to expand knowledge i want a macro which i assign to a button so that in one click job is done.


Again there is problem in cell range, when i record a macro it generate below code


Sub Macro1()

'

' Macro1 Macro

'


'

Range("E6:F6").Select

Selection.AutoFill Destination:=Range("E6:F10")

Range("E6:F10").Select

End Sub


so problem is when i enter new row & run macro it only calculate cell range between E6:F10.


To expand it i think if i assign nonblank cells value i.e. =COUNTA(D5:D100) to a cell & put the value of that instead of 10 it will work.


in my work book there is 6 non blank cells present so value of J5 is 6 & add 4 to it due to present in 5th row.

Total is 10 so that it will be F10.


http://www.2shared.com/file/zq__r5au/auto_cal.html

 

[Hui]

Sparcle


Try this instead

[pre]

Sub Macro1()
Dim LastRow As Long
LastRow = Cells(Rows.Count, "D").End(xlUp).Row
Range("E6:F6").AutoFill Destination:=Range("E6:F" & LastRow)
End Sub

[/pre]

Note there is no need to select the data first

 

[sparcle]

@Hui


Thank you sir

Thanks a lot, its working perfectly.