Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change event macro
Hi all,
I have a worksheet that has a about 58,000 rows and 15 coloums. (A-O) Columns A and C contain data that is used in the remaining columns. Unfortunately having live formulas in all the columns results in a really slow spreadsheet. What I would like is to have the formula reside in only the first row colum D-O And then if on a particular row a change is made to column A or C the macro would copy that formula from the first row and past it into the relevant row and turn the result into a value. That way Excel only calculates for that row. I have a very vague idea of how to use the change_event . E.g (where f1-f12 are formulas) a b c d e f g h i j k l m n o 1 20 C 10 f1 f2 f3 f4 f5 f6 f7 f8 f9 f10 f11 f12 2 10 D 40 3 30 S 10 e.g A change to colum A or C results in f1 to f12 being copied here and turned to values All help is greatly appreciated Naz |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change event macro
Naz,
Copy the code below, right click the sheet tab of your worksheet, select "View Code", and paste the code into the window that appears. Assumes that your 12 formulas start in cell D1: change the address in the code if they actaully start in a different row. HTH, Bernie MS Excel MVP Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Target.Column 3 Then Exit Sub Application.EnableEvents = False Range("D1").Resize(1, 12).Copy Cells(Target.Row, 4) Cells(Target.Row, 4).Resize(1, 12).Value = _ Cells(Target.Row, 4).Resize(1, 12).Value Application.EnableEvents = True End Sub "Jimbola" wrote in message ... Hi all, I have a worksheet that has a about 58,000 rows and 15 coloums. (A-O) Columns A and C contain data that is used in the remaining columns. Unfortunately having live formulas in all the columns results in a really slow spreadsheet. What I would like is to have the formula reside in only the first row colum D-O And then if on a particular row a change is made to column A or C the macro would copy that formula from the first row and past it into the relevant row and turn the result into a value. That way Excel only calculates for that row. I have a very vague idea of how to use the change_event . E.g (where f1-f12 are formulas) a b c d e f g h i j k l m n o 1 20 C 10 f1 f2 f3 f4 f5 f6 f7 f8 f9 f10 f11 f12 2 10 D 40 3 30 S 10 e.g A change to colum A or C results in f1 to f12 being copied here and turned to values All help is greatly appreciated Naz |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change event macro
First, a little overview of event procedures: Many objects have "events"
that can trigger code. Think about a button: Excel needs some way of knowing when the button has been pushed, then some way of knowing what to do with it. You put this in a ButtonName_Click() procedure in a standard module and Excel knows to run the code when it sees the event. Likewise, there is a Change event for any individual worksheet that "fires" when there is a change to the contents anywhere on that sheet (with some exceptions, such as changes caused by code). Excel even will provide you with the range that was changed (even if multiple cells, such as in copying and pasting a range). You need to put the event procedure in the worksheet module - by selecting the worksheet in the project explorer and double-clicking on it. This code steps through the rows in any range that was changed: Sub Worksheet_Change(ByVal Target as Range) Dim ThisRow as Range 'To step through the rows in Target, if Target is multiple cells Dim RowNo as Integer For Each ThisRow in Target.Cells.Rows.EntireRow RowNo = ThisRow.Row ' Do Copy/Paste here Next ThisRow End Sub As you can see, ThisRow.Row contains the row number of the destination. To do a copy and paste, include another Range variable, NewRange, in your Dim statements: If RowNo <1 Then 'this makes sure you DO NOT CHANGE THE FIRST ROW! Set NewRange = Range("D1:O1") NewRange.Copy With NewRange.Offset(RowNo -1, 0) PasteSpecial xlPasteFormulas .Copy .PasteSpecial xlPasteValues End With End If Hope I got the code right, no time to test it for you here. But also, hopefully it is clear enough for you to figure out anything that needs tweaking... HTH! "Jimbola" wrote: Hi all, I have a worksheet that has a about 58,000 rows and 15 coloums. (A-O) Columns A and C contain data that is used in the remaining columns. Unfortunately having live formulas in all the columns results in a really slow spreadsheet. What I would like is to have the formula reside in only the first row colum D-O And then if on a particular row a change is made to column A or C the macro would copy that formula from the first row and past it into the relevant row and turn the result into a value. That way Excel only calculates for that row. I have a very vague idea of how to use the change_event . E.g (where f1-f12 are formulas) a b c d e f g h i j k l m n o 1 20 C 10 f1 f2 f3 f4 f5 f6 f7 f8 f9 f10 f11 f12 2 10 D 40 3 30 S 10 e.g A change to colum A or C results in f1 to f12 being copied here and turned to values All help is greatly appreciated Naz |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
change event macro to add cells | Excel Worksheet Functions | |||
Referring to Ranges in Change-Event Macro? | Excel Discussion (Misc queries) | |||
Change event Macro | Excel Discussion (Misc queries) | |||
Event Macro adjustment needed - need to change font color also | Excel Worksheet Functions | |||
Copy Sheets minus Worksheet Change Event code & Macro Buttons | Excel Programming |