Excel crashing after adding Macro

Question

I added a simple Macro to my Excel, but it seems to make my Excel crash a lot. Does anyone know why?

Here's the macro:

Sub PlanRelativityTesting()
With Sheets("Sheet1")
For i = 8 To 9
.Range("D11").value = .Range("G" & i).value          
Call AnotherMacro  
Sheets("Sheet2").Select    
        .Range("H" & i).value = Sheets("Sheet2").Range("AE20").value
        .Range("I" & i).value = Sheets("Sheet2").Range("AE21").value
        .Range("J" & i).value = Sheets("Sheet2").Range("AE22").value
        .Range("K" & i).value = Sheets("Sheet2").Range("AE23").value
        .Range("L" & i).value = Sheets("Sheet2").Range("AE24").value
        .Range("M" & i).value = Sheets("Sheet2").Range("AE25").value
        .Range("N" & i).value = Sheets("Sheet2").Range("AE26").value
        .Range("O" & i).value = Sheets("Sheet2").Range("AE27").value
        .Range("P" & i).value = Sheets("Sheet2").Range("AE28").value
        .Range("Q" & i).value = Sheets("Sheet2").Range("AE29").value
        .Range("R" & i).value = Sheets("Sheet2").Range("AE30").value
        .Range("S" & i).value = Sheets("Sheet2").Range("AE31").value
Next i       
.Select   
End With
End Sub

Why don't you just assign the transpose of the one range to the other? Also, since your code refers to some mysterious AnotherMacro -- how can anybody say what the problem is? The code that you show is unproblematic (though needlessly verbose). — John Coleman
– John Coleman, Commented Nov 16, 2016 at 17:48
Have you checked the called function as well? Depending on how large your sheet is and the version of excel (x64 or x86), you could be running against the 2 GB RAM limit. You might also consider using a relative address for the cells so you don't have to type so much code, ie, Range(Cells(1, 1), Cells(5, 3)) is A1:C5 and allows looping over both row and column. The transpose is a simpler solution if that's what you are trying to do. — Zediiiii
– Zediiiii, Commented Nov 16, 2016 at 17:50
hi Zediiiii , Thank you for the comment! It's running on 32 bit Excel, and it's 39 KB. So the size probably isn't the issue? Could you think of another reason? — JBB
– JBB, Commented Nov 22, 2016 at 19:12
So I tried it a few more times. The original Excel model works, and the macro also works. But whenever I saved as a new copy, it would crash when I open and enable macro. Thanks ! — JBB
– JBB, Commented Nov 22, 2016 at 19:42

Tim Williams · Accepted Answer · 2016-11-16 17:55:57Z

1

As John notes your code could be more concise:

Sub PlanRelativityTesting()
    With Sheets("Sheet1")
        For i = 8 To 9
            .Range("D11").Value = .Range("G" & i).Value
            AnotherMacro
            .Range("H" & i).Resize(1, 12).Value = _
            Application.Transpose(Sheets("Sheet2").Range("AE20").Resize(12, 1).Value)
        Next i
        .Select
    End With
End Sub

The actual issue though is more likely to be in AnotherMacro

answered Nov 16, 2016 at 17:55

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

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

2 Comments

JBB Over a year ago

Thank you for your help! AnotherMacro should be ok; it has been in the Excel model for a long time now. It seems to work better when I run the macro in the original model. But once I save another copy of the model with the macro, it tends to crash easily. The file is 39,000 KB, maybe that's why?

Tim Williams Over a year ago

It's impossible to say whether that's the cause, but 39MB is pretty large.

Collectives™ on Stack Overflow

Excel crashing after adding Macro

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related