# Macro Freezes on me

#### Brentsa

##### Board Regular
Im trying to run the follwing and each time that I do I have to quit Excel and restart as Excel freezes on me. Not sure if I've written something wrong in my code?

VBA Code:
``````Sub Balance()
'Move Calculator Results
Sheets("Balance").Activate
Dim Cl As Range
Dim Lst As Object
Dim UsdRws As Long

Set Lst = CreateObject("system.collections.arraylist")
With Sheets("Calculator")
UsdRws = .Range("D:D").Find("*", , , , xlByRows, xlPrevious, , , False).Row
For Each Cl In .Range("D1:D" & UsdRws)
If IsNumeric(Cl.Value) And Cl.Value > 0 Then
End If
Next Cl
End With
Lst.Sort
Sheets("Balance").Range("aa1").Resize(Lst.Count).Value = Application.Transpose(Lst.toarray)
Columns("AA:AA").Activate
Selection.Style = "Comma"
Range("AB1").Activate
'Create Formula in AB1
Dim LR As Long

LR = Cells(Rows.Count, "AA").End(xlUp).Row
With Range("AB:AB" & LastRowColumnA)
.FORMULA = "=IFERROR(INDEX('Teller Stats'!C6,AGGREGATE(15,6,ROW('Teller Stats'!C6)/((('Teller Stats'!C9=Balance!RC27)+('Teller Stats'!C10=Balance!RC27))>0)/ISNA(MATCH('Teller Stats'!C6,Balance!RC27:RC[-1],0)),1)),"""")"
.NumberFormat = "0"
End With

End Sub``````

Last edited by a moderator:

### Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying

#### Fluff

##### MrExcel MVP, Moderator
I'm not surprised Excel freezes you are using an array formula that looks at entire columns, which is not a good, but you are then putting that formula into the entire column AB all 1,048,576 cells.
Try
VBA Code:
``With Range("AB1:AB" & LR)``
I would also recommend you change the formula so you are not looking at entire columns.

#### Brentsa

##### Board Regular
Thanks Fluff missed that

#### Fluff

##### MrExcel MVP, Moderator
You're welcome & thanks for the feedback.

Replies
1
Views
87
Replies
4
Views
706
Replies
0
Views
197
Replies
3
Views
134
Replies
1
Views
263

1,136,445
Messages
5,675,900
Members
419,591
Latest member
mersanko

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back