Error 91 in VBA

[sjk1193]

Hi there,

Im getting a runtime error 91 in VBA and cant figure out where the probelm is. I've used this code before and now its not working

Sub Macro2()


' Defines variables
Dim InputFile As Workbook
Dim OutputFile As Workbook


' Open input / output workbooks:
    With Application.FileDialog(msoFileDialogFilePicker)
        'Makes sure the user can select only one file
        .AllowMultiSelect = False
        .InitialFileName = "S:\PQfolders\Investor Lists"
        'Filter to just the following types of files to narrow down selection options
        .Filters.Add "Excel Files", "*.xlsx; *.xlsm; *.xls; *.xlsb", 1
        .Show
        
        'Store in filepath variable
        filepath = .SelectedItems.Item(1)
    End With
    
Set InputFile = Workbooks.Open(filepath)
Set OutputFile = ThisWorkbook


'-----------------------------------------------------------------------------


Dim Tradedate As Variant
Dim Value As Variant
Dim NBIN As Variant
Dim Name As Variant
Dim Trasaction As Variant
Dim security As Variant
Dim units As Variant
Dim EntityID As Variant
Dim Curr As Variant


Dim i As Long
Dim LR As Long




   InputFile.Sheets(1).Activate
   'inserts column with NBIN Account Number
        ActiveSheet.Cells.Find(what:="Direct Owner", SearchOrder:=xlByColumns).Offset(0, 1).EntireColumn.Insert
        Range("D1") = "NBIN Account Number"
        Range("D2").FormulaR1C1 = "=MID(RC[-1],FIND(""6C"",RC[-1]),7)"
            LR = Range("C" & Rows.Count).End(xlUp).Row
            Range("D2").AutoFill Destination:=Range("D2:D" & LR)
   'inserts column with number of units
        ActiveSheet.Cells.Find(what:="Transaction Units", SearchOrder:=xlByColumns).Offset(0, 1).EntireColumn.Insert
        Range("i1") = "Number of Units"
        Range("i2").FormulaR1C1 = "=ABS(RC[-1])"
            LR = Range("C" & Rows.Count).End(xlUp).Row
            Range("i2").AutoFill Destination:=Range("i2:i" & LR)
            


'finds the transaction column location
InputFile.Sheets(1).Activate
    ActiveSheet.Cells.Find(what:="Trade Date", SearchOrder:=xlByColumns).Select
    
    ' all the relative referances are based on the "Trade Date" column so we have to use the same column order
    For i = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
    If ActiveCell.Offset(i, 0) <> "" Then


                   Tradedate = ActiveCell.Offset(i, 0)
                   Value = ActiveCell.Offset(i, 1)
                   NBIN = ActiveCell.Offset(i, 3)
                   Name = ActiveCell.Offset(i, 4)
                   Trasaction = ActiveCell.Offset(i, 5)
                   security = ActiveCell.Offset(i, 6)
                   units = ActiveCell.Offset(i, 8)
                   EntityID = ActiveCell.Offset(i, 10)
                   Curr = ActiveCell.Offset(i, 11)


             OutputFile.Activate
                    
                    Range("c44") = Tradedate
                    Range("e44") = Value
                    Range("b29") = NBIN
                    Range("a17") = Name
                    Range("b44") = Trasaction
                    Range("a44") = security
                    Range("j44") = units
                    Range("b31") = EntityID
                    Range("d44") = Curr
                    
            
                ActiveSheet.Name = Range("b29")
                ActiveSheet.Copy After:=ActiveSheet
                'Range("A1:J62").Copy
                
                'Worksheets.Add After:=ActiveSheet
                'ActiveSheet.Paste
                      
        InputFile.Activate
        
        End If
    
    Next


End Sub

 

[Norie]

Which line of code causes the error?

 

[sjk1193]

thats what I'm not sure about. When I F8 into the code the it runs the code until '-----------------------------------------------------------
but then the runtime error message pops up so it doesnt highlight where the error is

 

[Norie]

There's no option to Debug when the error message pops up?