Subscipt out of range error

    Hi,


    I am completely new to VBA so please forgive the poor us of technical language and code you are about to see. I keep getting an error when running the macro saying subscript out of reach. Where am I going wrong in my code?



    Sub Complete()
    Dim rng As Range
    Dim calcmode As Long
    Dim myArr As Variant
    Dim I As Long


    With Application
    calcmode = .Calculation
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
    End With


    myArr = Array("Withdrawal", "Event", "Dividend")


    For I = LBound(myArr) To UBound(myArr)


    With ActiveSheet


    .AutoFilterMode = False


    .Range("B1:B" & .Rows.Count).AutoFilter Field:=1, Criteria1:=myArr(I)


    Set rng = Nothing
    With .AutoFilter.Range
    On Error Resume Next
    Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
    .SpecialCells(xlCellTypeVisible)
    On Error GoTo 0
    If Not rng Is Nothing Then rng.EntireRow.Delete
    End With


    .AutoFilterMode = False
    End With


    Next I


    With Application
    .ScreenUpdating = True
    .Calculation = calcmode
    End With


    Cells.Replace What:="Sale", Replacement:="S", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
    Cells.Replace What:="Purchase", Replacement:="B", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
    Columns("A:A").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Portfolio Name"
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "Destination 2"
    Range("A2").Select
    Selection.Copy
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.Paste
    Cells.Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("BBU Macro.xlsm").Activate
    Cells.Select
    ActiveSheet.Paste
    Sheets("Settings").Select
    Application.Run "ConnectChartEvents"


    End Sub





    Best,
    Jack

    When you get an error like that, click on Debug and it will highlight the line of code that threw the error message. In this case, I suspect that's the Windows("BBU Macro.xlsm").Activate line, which could be due to that workbook not being open.

    Hi Jonathan,
    Is there a line of code I can add to open this file and then close it again? Or would I just need to leave it open as I suspect you are correct?
    Jack

    The macro recorder doesn't record keystrokes, it translates your actions into the equivalent VBA statements. If the button is not a custom button, and it creates an action, then that action should be translated into VBA code (although Microsoft does not cover everything). The VBA SendKeys statement can be used to send end keystrokes to the program as if they were typed, so if there is a accelerator key assigned to the button, that could work.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login