| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
troubleshoot column formula
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! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
troubleshoot column formula
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: 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! |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
troubleshoot column formula
where do i have to paste the code? in which menu should i go?
Ο χρήστης "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: 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! |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
troubleshoot column formula
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: where do i have to paste the code? in which menu should i go? Ο χρήστης "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: 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! |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
troubleshoot column formula
thank you! it works fine! how do i enter the same for another column? (i have
two columns with formulas. Ο χρήστης "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: where do i have to paste the code? in which menu should i go? Ο χρήστης "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: 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! |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
troubleshoot column formula
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: thank you! it works fine! how do i enter the same for another column? (i have two columns with formulas. Ο χρήστης "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: where do i have to paste the code? in which menu should i go? Ο χρήστης "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: 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! |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
troubleshoot column formula
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 he 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. angie wrote: 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! -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Troubleshoot | Excel Worksheet Functions | |||
| SUM troubleshoot | Excel Discussion (Misc queries) | |||
| Troubleshoot help | Excel Discussion (Misc queries) | |||
| troubleshoot a workbook for me? | Excel Discussion (Misc queries) | |||
| troubleshoot custom Excel Auto Filters when the column is a # or d | Excel Worksheet Functions | |||
Linear Mode
