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
Lst.Add Cl.Value
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: