Change event macro

[Jimbola]

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

 

[Bernie Deitrick]

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

 

[K Dales]

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!