excel vba runtime error 7 - out of memory

Question

I have two macros in a worksheet. The first one check whether certain cells are addressed and have certain values then runs another macro. The following code is used for this:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("K10:K1000")) Is Nothing And Target.Value = "Trapezoidal       roof 0.6mm and above" Or Target.Value = "LightBox ballasted" Then

Application.ScreenUpdating = False

Call PPAPricePerkWp

End If

End Sub

This works fine on it's own.

The second macro is run when a button is clicked. This macro copies and pastes cells/rows to other parts of the spreadsheet.

When the macro is run I get the error Runtime error 7 - out of memory and it breaks on the above bit of code.

Is there another way I can check whether cells in a certain column are addressed and have certain values and won't lead to the above error?

It checks a cell value for a cell in column k, does some calculations then pastes a value to the cell below. The debugger breaks at the If Not Target line so PPAPricePerkWp isn't being run. — evoandy
– evoandy, Commented Jan 17, 2013 at 17:22
you might want to disable events before you call your subroutine, so that the Worksheet_Change is not being triggered every time you change a cell — SeanC
– SeanC, Commented Jan 17, 2013 at 17:23
thanks @SeanCheshire that works. I think the second macro was deleting values from a large range of cells so was running out of memory. Do you want to put it as an answer so I can mark it up. — evoandy
– evoandy, Commented Jan 17, 2013 at 17:31

SeanC · Accepted Answer · 2013-01-17 17:33:19Z

5

you might want to disable events before you call your subroutine, so that the Worksheet_Change is not being triggered every time you change a cell

Application.EnableEvents = False

Don't forget to turn it back on when you are finished

answered Jan 17, 2013 at 17:33

SeanC

16k5 gold badges48 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

excel vba runtime error 7 - out of memory

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related