• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need Command / Formula / Function / Macro for Autofill

skarnik01

skarnik01

Member
Hello,

I am having a set of values (around 15 from D1:R1) which are dependent on 3 values - A1:C1. Whenever I add a row with these 3 values, I need this range D1:R1 to get auto-populated based on the calculations done from the columns A to C. It includes Sums, Averages etc on values in columns A:C.
Problem: I always require to use file handle to update these values as the rows keep increasing. The update to these values have reference to other worksheets and thereby missing on updating through file handle would give me wrong results.

Is there any command / formula / function / macro that can help me autofill these values?

Please note that cells D1:R1 already have very complex formulas present and I am not keen on editing these cells again. Also I cannot use fill handle on these columns (D:R) in advance since they would calculate on blank values of A:C and give me wrong results.

Thanks in advance,

Regards,
SK
 
Hi Sk..

very difficult to get the scenario.. you want..:(
Can you please post a sample file with expected output..
 
Attaching a sample file for clarity. What I need is that when I enter values in columns A to C (in this example A11:C11), values in D11:F11 should get automatically calculated. At times Autofill does allow automatic filling of values but I am experiencing inconsistency in it.
Also this should occur for all rows added in future and should not remain restricted to this row only.
 

Attachments

Last edited:
Added a Macro basis your requirements. See attached file.

Basically it clears columns D to F first, write the formulas and then value pastes them

Code: 
Sub calculator()
 
'Clear Columns D to F
Range("D:F").Select
Selection.ClearContents
 
'Write sum formula in D4
Range("D4").Formula = "=SUM(C1:C4)"
 
'Write Average formula in E4
Range("E4").Formula = "=AVERAGE(C2:C4)"
 
'Write IF formula in F4
Range("F4").Formula = "=IF(C4<C3,""Yes"",""No"")"
 
'Select Drag Down range
Range("C1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, 1).Select
Range(Selection, Selection.Offset(0, 2)).Select
Range(Selection, Selection.End(xlUp)).Select
 
'Drag down formulas
Selection.FillDown
 
'Value paste the formulas
Selection.Copy
Selection.PasteSpecial xlPasteValues
 
End Sub
 

Attachments

Hi,

Thanks for the Macro.
I would like to know if it would delete records in Columns D:F everytime I run a Macro? The reason for asking is that the file I would be running the Macro on, would be having more than 2000 rows and more than 55-60 columns. I suspect an impact on the performance due to this everytime I add or update a record.
 
You must log in or register to reply here.
Back
Top