Formula automation

  • Thread starter Thread starter Marcella
  • Start date Start date
M

Marcella

Hi all,

I want to create a formula automation so when a user enter a data
through the form that I've created, the data will go straight to my
Excel database and Excel should automatically calculate them using the
formula from the previous row. I have tried to go to
"Tools-options-edit-extend formula" and ticked the box but it won't do
anything.

Does anyone has any idea on how to do this automation thing?
Thanks a lot!

Rgds,
Marcella

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins &
Software!http://www.ozgrid.com/Services/excel-software-categories.htm **
 
Marcella, why reinvent the wheel? What you are trying to do is exactly what
the Form command on the Data menu is designed to do. So long as your data is
organized by columns and rows in a contiguous set of cells with column
labels at the top, all your users have to do is click in the data and Data >
Form. Use the form to add data, then click Close. Excel adds the data to the
bottom of the list and copies any formulas down, just as you want.
 
Hi Marcella,

You can use a macro to copy the previous row, and remove the
constants. For an example of a stand alone macro that you
would only use parts of see
Insert a Row using a Macro to maintain formulas
http://www.mvps.org/dmcritchie/excel/insrtrow.htm

The following event macro will insert a new row below and copy formulas.
Rework the code for your macro by changing Target. to your own range.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True 'Eliminate Edit status due to doubleclick
Target.Offset(1).EntireRow.Insert
Target.EntireRow.Copy ActiveCell.Offset(1).EntireRow
Target.Offset(1).EntireRow.SpecialCells(xlConstants).ClearContents
End Sub
 
Back
Top