troubleshoot column formula

[angie]

i have pasted a fomula in an entire column. the problem is that excel has
become very slow on open and save and i only have 100 records in total. is
there a way not to copy the formula in the entire column but each time a user
enters data in a row the corresponding cell in that row gets the fomula?

your help would be greatly appreciated!

 

[Stefi]

A Worksheet_Change event macro like this may help:
Private Sub Worksheet_Change(ByVal Target As Range)
Range("E" & Target.Row).FormulaR1C1 = Range("E" & Target.Row -
1).FormulaR1C1
End Sub

It takes the formula from the previous row.
Change "E" to column letter needed!


Regards,
Stefi

„angie†ezt írta:

 

[angie]

where do i have to paste the code? in which menu should i go?


Ο χÏήστης "Stefi" έγγÏαψε:

 

[Stefi]

Alt+F11 (VBA)
Right click on worksheet name in the Project Explorer window
Choose View code from the local menu
Copy/Paste code in the code window

Stefi


„angie†ezt írta:

 

[angie]

thank you! it works fine! how do i enter the same for another column? (i have
two columns with formulas.

Ο χÏήστης "Stefi" έγγÏαψε:

 

[Stefi]

Private Sub Worksheet_Change(ByVal Target As Range)
Range("E" & Target.Row).FormulaR1C1 = Range("E" & Target.Row -
1).FormulaR1C1
Range("F" & Target.Row).FormulaR1C1 = Range("F" & Target.Row -
1).FormulaR1C1
Range("G" & Target.Row).FormulaR1C1 = Range("G" & Target.Row -
1).FormulaR1C1
End Sub

Change "E", "F", "G" to column letters needed!

Stefi


„angie†ezt írta:

 

[Dave Peterson]

Personally, I try to figure out how many rows I'll use and then double it and a
little more <vbg>. I wouldn't use the entire column.

But you could use a macro that does the work for you.

David McRitchie shares some code:
http://www.mvps.org/dmcritchie/excel/insrtrow.htm
look for: InsertRowsAndFillFormulas

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

===========
You may want to experiment with lists (xl2003) or tables (xl2007), too.

And although I've never had good luck with this setting:
Tools|Options|Edit tab|"Extend data range formats and formulas"
You may want to see if it works for you.