Subscript out of range (Error)

KhallP

Board Regular
Joined
Mar 30, 2021
Messages
80
Office Version
  1. 2016
Platform
  1. Windows
I created a procedure to detect and open the last file inserted in a specific folder
when I run the program an error is displayed, can someone help me?



The code:

VBA Code:
Private Sub WriteValues()


Private Sub recentFilesSpecificFolder()
Dim myFile As String, myMostRecentFile As String, myRecentFile As String, myDirectory As String, fileExtension As String
Dim recentDate As Date

myDirectory = Environ("userprofile") & "\Documents\Projeto_Luis\André\EEC\QEIM\QEIM_geral"
fileExtension = " * .xls"

If Right(myDirectory, 1) <> "\" Then myDirectory = myDirectory & "\"

myFile = Dir(myDirectory & fileExtension)
If myFile <> "" Then
    myRecentFile = myFile
    recentDate = FileDateTime(myDirectory & myFile)
    Do While myFile <> ""
        If FileDateTime(myDirectory & myFile) > recentDate Then
             myRecentFile = myFile
             recentDate = FileDateTime(myDirectory & myFile)
         End If
         myFile = Dir
    Loop
End If
myMostRecentFile = myRecentFile
Workbooks.Open Filename:=myDirectory & myMostRecentFile
End Sub


End Sub




The error:

Capturar.JPG




Note: I forgot to mention, the code is executed when the exel file is opened
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,730
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
I can't see any line there that would cause that error. Which line is highlighted when you debug?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,730
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Is there code in the Workbook_Open event of the workbook you are opening? I think the error is in that code.
 

KhallP

Board Regular
Joined
Mar 30, 2021
Messages
80
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Yes there is, here it is


VBA Code:
Private Sub Workbook_Open()

Call AutoRunMacro

End Sub
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,730
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
What's the code for AutoRunMacro? Simple thing to do: in the VB Editor, click Tools - Options, then on the General tab change the error handling setting to 'Break in class module'. Now run your original code again, and it should break on the actual error line.
 

KhallP

Board Regular
Joined
Mar 30, 2021
Messages
80
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Is there code in the Workbook_Open event of the workbook you are opening? I think the error is in that code.

In the meantime a different error arose, I believe that I am not doing what I want in the right way, as I said I want to open the last changed / created file in a specific folder, then I want to assign values in the cells of that file, I am not yet very experienced in VBA and I think that the problem is with the code, could you help me?




Code:
Private Sub WriteValues()

    ActiveWorkbook.Sheets("QEIM C21").Select
    ActiveCell = Range("AC12").Select

    If ActiveCell.Value = "" Then
        ActiveCell.Select
        With Selection
            .Name = "Date"
            .HorizontalAlignment = xlRight
        End With

        Range("AD12").Select
        With Selection
            .Name = "Time"
            .HorizontalAlignment = xlRight
        End With

        Range("AE12").Select
        With Selection
            .Name = "Value"
        End With

        Else
        End If


    Range("AC13").Select

    If ActiveCell.Value = "" Then
        Call FillCells

    Else
        Do Until ActiveCell.Value = ""
            ActiveCell.Offset(1, 0).Select
        Loop
            Call FillCells

End If
End Sub



Sub FillCells()

    ActiveCell.Value = Range("C13").Value
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = WorksheetFunction.Sum(Range("W13:W108"), Range("AA13:AA108"))
    ActiveCell.Offset(0, 1).Select
    ActiveCell.Value = "kWh"
   
End Sub



Private Sub recentFilesSpecificFolder()
Dim myFile As String, myMostRecentFile As String, myRecentFile As String, myDirectory As String, fileExtension As String
Dim recentDate As Date

myDirectory = Environ("userprofile") & "\Documents\Projeto_Luis\André\EEC\QEIM\QEIM_geral"
fileExtension = " * .xls"

If Right(myDirectory, 1) <> "\" Then myDirectory = myDirectory & "\"

myFile = Dir(myDirectory & fileExtension)
If myFile <> "" Then
    myRecentFile = myFile
    recentDate = FileDateTime(myDirectory & myFile)
    Do While myFile <> ""
        If FileDateTime(myDirectory & myFile) > recentDate Then
             myRecentFile = myFile
             recentDate = FileDateTime(myDirectory & myFile)
         End If
         myFile = Dir
    Loop
End If
myMostRecentFile = myRecentFile
Workbooks.Open Filename:=myDirectory & myMostRecentFile
End Sub



Sub AutoRunMacro()
       
Call recentFilesSpecificFolder
Call WriteValues

End Sub




Capturar.JPG



Sorry for not specifying it before
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,730
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
You've got the sheet name wrong, or the wrong workbook active if you get a subscript error on that line.
 

KhallP

Board Regular
Joined
Mar 30, 2021
Messages
80
Office Version
  1. 2016
Platform
  1. Windows
You've got the sheet name wrong, or the wrong workbook active if you get a subscript error on that line.
FilePath: D:\Users\AFCALME\Documents\Projeto_Luis\André\EEC\QEIM\QEIM_geral

Sheet of the other file:

Capturar.JPG


everything is apparently correct ...
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,730
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Than I guess that workbook is not active at the time you run the code.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,182
Messages
5,640,665
Members
417,160
Latest member
Timon82

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top