Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 24

Thread: Subscript Out of range VBA Error

  1. #1
    New Member
    Join Date
    May 2011
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Smile Subscript Out of range VBA Error

    Hi all,
    If it is possible, i would like to know what the 'Subscript out of range error means' when trying to compile a VBA code?
    I changed the code that i pasted below from what i found on the internet, according to my needs.

    Does anyone know what this means? When i hit the debug button it highlights this line:

    Set wsMaster = ThisWorkbook.Sheets("Master") 'sheet report is built into


    Here is my code
    Sub Consolidate()

    'Summary: Prompt user to open folder
    'Open file, delete unwanted data, filter latitudes
    'Longitudes to fit South Australian region

    Dim fName As String, fPath As String, fPathDone As String
    Dim LR As Long, NR As Long
    Dim wbData As Workbook, wsMaster As Worksheet

    'Setup
    Application.ScreenUpdating = False 'speed up macro execution
    Application.EnableEvents = False 'turn off other macros for now
    Application.DisplayAlerts = False 'turn off system messages for now

    Set wsMaster = ThisWorkbook.Sheets("Master") 'sheet report is built into

    With wsMaster
    If MsgBox("Clear the o data first?", vbYesNo) = vbYes Then
    .Cells.Clear
    NR = 1
    Else
    NR = .Range("A" & .Rows.Count).End(xlUp).Row + 1 'appends data to existing data
    End If

    'Path and filename
    'Prompting user to choose the required folder

    MsgBox "Please select a folder with files to consolidate"
    Do
    With Application.FileDialog(msoFileDialogFolderPicker)
    .AllowMultiSelect = False
    .Show
    If .SelectedItems.Count > 0 Then
    fPath = .SelectedItems(1) & "\"
    Exit Do
    Else
    If MsgBox("No folder chosen, do you wish to exit Macro?", _
    vbYesNo) = vbYes Then Exit Sub
    End If
    End With
    Loop

    fPathDone = fPath & "Imported\" 'remember final \ in this string
    On Error Resume Next
    MkDir fPathDone 'creates the completed folder if missing
    On Error GoTo 0
    fName = Dir(fPath & "*.csv*") 'listing of desired files


    'Import a sheet from found files
    Do While Len(fName) > 0
    If fName <> ThisWorkbook.Name Then 'don't reopen this file accidentally
    Set wbData = Workbooks.Open(fPath & fName) 'Open file

    'This is the section to customize, replace with your own action code as needed

    Columns("F:I").Select
    Selection.ClearContents
    Columns("A:E").Select
    Selection.AutoFilter
    Columns("C:C").Select 'Filter Latitude
    ActiveSheet.Range("$A$1:$E$31001").AutoFilter Field:=3, Criteria1:= _
    ">=-37.9382", Operator:=xlAnd, Criteria2:="<=-32.004"
    Columns("D:D").Select 'Filter Longitude
    ActiveSheet.Range("$A$1:$E$31001").AutoFilter Field:=4, Criteria1:= _
    ">=136.7754", Operator:=xlAnd, Criteria2:="<=141.0698"
    Cells.Select
    Selection.Copy
    Workbooks.Add

    wbData.Close False 'close file
    NR = .Range("A" & .Rows.Count).End(xlUp).Row + 1 'Next row
    Name fPath & fName As fPathDone & fName 'move file to IMPORTED folder
    fName = Dir 'ready next filename
    End If
    Loop
    End With

    ErrorExit: 'Cleanup
    ActiveSheet.Columns.AutoFit
    Application.DisplayAlerts = True 'turn system alerts back on
    Application.EnableEvents = True 'turn other macros back on
    Application.ScreenUpdating = True 'refreshes the screen
    End SubI will be delighted if someone can find the error or kindly explain what the error means

  2. #2
    Board Regular
    Join Date
    Oct 2003
    Posts
    1,325
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Subscript Out of range VBA Error

    Does your workbook contained a worksheet named 'Master' (without the quote marks)?

  3. #3
    Board Regular
    Join Date
    Feb 2010
    Location
    London, UK
    Posts
    8,685
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    2 Thread(s)

    Default Subscript Out of range VBA Error

    Do you have a sheet called: "Master" in the workbook you're running the macro on? That error means it can't find something it's been asked to find from the code...


  4. #4
    New Member
    Join Date
    Oct 2010
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Subscript Out of range VBA Error

    This line:

    Set wsMaster = ThisWorkbook.Sheets("Master") 'sheet report is built into

    stores the Worksheet named "Master" in the wsMaster variable. The excel is complaining probably because there is no worksheet name "Master" in your file!
    You need to either name a sheet "Master" or change the code.

  5. #5
    New Member
    Join Date
    May 2011
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Subscript Out of range VBA Error

    Thank you so much for the quick reply!

    That bit of the code now works, in fact the entire code compiles with no run time errors.

    However, for each file that it reads it opens an entirely new workbook, yet i want it it to select the filtered data from each file and paste into a common worksheet. One row after the other. That is, i need the information from each file found in the folder, imported into a single workbook. How do i do this?

    Also, at the moment, there is no data being transferred, and i have checked and for most of the files chosen my conditionally ranges do include some data. I am thinking that it does not work because the section of the code which is meant to conditionally select the required data was 'recorded' and not actually coded manually.
    I think that
    [code]
    ActiveSheet.Range("$A$1:$E$31001").AutoFilter Field:=3, Criteria1:= _
    ">=-37.9382", Operator:=xlAnd, Criteria2:="<=-32.004" [code\]
    where it says ActiveSheet it should maybe refer to the sheet of the workbook is opening...i am unsure of what this ActiveSheet section of the code is referring to.

    Could you yet again point me in the right direction, please?
    Kind Regards

  6. #6
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    73,725
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Subscript Out of range VBA Error

    To stop the new workbooks getting created just remove this.
    Code:
    Workbooks.Add
    I think you'll need to explain the rest a bit more.

    It looks like you want to open a bunch of files, extract some (filtered?) data from them and copy it somewhere, the workbook the code is in perhaps?
    If posting code please use code tags.

  7. #7
    New Member
    Join Date
    May 2011
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Subscript Out of range VBA Error

    Yes. that is right.
    I have a folder with approximately 200 csv. files.
    Each has 9 columns, of which A:E are useful, the rest of the columns can be deleted.
    COlumns C and D contain data from which in only need a specific range of values. They contain latitude and longitude, and i have min and max values that these have to satisfy. Once i have the data filtered, and i want to copy it into a brand new workbook.
    for the next file in the folder i want to do the same thing, but past the filtered values in the next available row of the brand new workbook created earlier.

    I got rid of the workbook.add section
    I tried running it, and it now comes up with an error saying
    Code:
    Object Variable or With Block variable not set
    when i hit debug it points to this line
    Code:
     NR = .Range("A" & .Rows.Count).End(xlUp).Row + 1    'appends data to existing data
    I am unsure what NR means, since i got that section from another code which i changed only slightly to incorporate my filtering.

    What could be the problem?
    Regards,
    Trini

  8. #8
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    73,725
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Subscript Out of range VBA Error

    Trini

    NR is just a variable name.

    In this case NextRow would probably be more descriptive that's what it's supposed to represent - the next available row to copy into.

    Try this, it's untested right now - I don't have any spare latitude/longitude CSV kicking about.

    I think it should work though I'm not sure it's going to do the filtering properly - I seem to recall there some sort of SpecialCells(xlVisibleCells) needed somewhere.
    Code:
    Option Explicit
     
    Sub Consolidate()
    'Summary: Prompt user to open folder
    'Open file, delete unwanted data, filter latitudes
    'Longitudes to fit South Australian region
    Dim fName As String, fPath As String, fPathDone As String
    Dim LR As Long, NR As Long
    Dim wbData As Workbook, wsMaster As Worksheet
    Dim wsSrc As Worksheet
     
        'Setup
    Application.ScreenUpdating = False    'speed up macro execution
    Application.EnableEvents = False    'turn off other macros for now
    Application.DisplayAlerts = False    'turn off system messages for now
     
    Set wsMaster = ThisWorkbook.Sheets("Master")    'sheet report is built into
    
        If MsgBox("Clear the old data first?", vbYesNo) = vbYes Then
            wsMaster.Cells.Clear
            NR = 1
        Else
            NR = wsMaster.Range("A" & wsMaster.Rows.Count).End(xlUp).Row + 1    ' gets next availablerow on the 'Master' Worksheet
        End If
     
        'Path and filename
        'Prompting user to choose the required folder
        MsgBox "Please select a folder with files to consolidate"
    
        Do
            With Application.FileDialog(msoFileDialogFolderPicker)
                .AllowMultiSelect = False
                .Show
                If .SelectedItems.Count > 0 Then
                    fPath = .SelectedItems(1) & "\"
                    Exit Do
                Else
                    If MsgBox("No folder chosen, do you wish to exit Macro?", _
                              vbYesNo) = vbYes Then Exit Sub
                End If
            End With
        Loop
     
        fPathDone = fPath & "Imported\"    'remember final \ in this string
        
        On Error Resume Next
        MkDir fPathDone    'creates the completed folder if missing
        On Error GoTo 0
        
        fName = Dir(fPath & "*.csv*")    'listing of desired files
    
        'Import a sheet from found files
        Do While Len(fName) > 0
        
            If fName <> ThisWorkbook.Name Then    'don't reopen this file accidentally
     
                Set wbData = Workbooks.Open(fPath & fName)    'Open file
     
                Set wsSrc = wbData.Worksheets(1)
    
                'This is the section to customize, replace with your own action code as needed
     
                wsSrc.Columns("F:I").ClearContents
    
                With wsSrc
                    .Columns("A:E").AutoFilter
    
                    'Filter Latitude
                    .Range("$A$1:$E$31001").AutoFilter Field:=3, Criteria1:= _
                                                       ">=-37.9382", Operator:=xlAnd, Criteria2:="<=-32.004"
    
                    'Filter Longitude
     
    
                    .Range("$A$1:$E$31001").AutoFilter Field:=4, Criteria1:= _
                                                                  ">=136.7754", Operator:=xlAnd, Criteria2:="<=141.0698"
     
                    .Range("A:E").Copy wsMaster.Range("A" & NR)
                    
                End With
                
                wbData.Close False    'close file
    
                NR = wsMaster.Range("A" & wsMaster.Rows.Count).End(xlUp).Row + 1   
    
                Name fPath & fName As fPathDone & fName    'move file to IMPORTED folder
    
                fName = Dir    'ready next filename
    
            End If
    
        Loop
     
        wsMaster.Columns.AutoFit
        
        Application.DisplayAlerts = True    'turn system alerts back on
        Application.EnableEvents = True    'turn other macros back on
        Application.ScreenUpdating = True    'refreshes the screen
        
    End Sub
    If posting code please use code tags.

  9. #9
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    73,725
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Subscript Out of range VBA Error

    NR is just a variable name - looks like it stands for next row and is the next available row on the master sheet.

    Try this code, I'm afraid it's untested.

    It think it should work, but I'm also pretty sure I'm missing something in the filtering part.
    Code:
    Option Explicit
     
    Sub Consolidate()
    'Summary: Prompt user to open folder
    'Open file, delete unwanted data, filter latitudes
    'Longitudes to fit South Australian region
    Dim fName As String, fPath As String, fPathDone As String
    Dim LR As Long, NR As Long
    Dim wbData As Workbook, wsMaster As Worksheet
    Dim wsSrc As Worksheet
        'Setup
    Application.ScreenUpdating = False    'speed up macro execution
    Application.EnableEvents = False    'turn off other macros for now
    Application.DisplayAlerts = False    'turn off system messages for now
     
    Set wsMaster = ThisWorkbook.Sheets("Master")    'sheet report is built into
    
        If MsgBox("Clear the old data first?", vbYesNo) = vbYes Then
            wsMaster.Cells.Clear
            NR = 1
        Else
            NR = wsMaster.Range("A" & wsMaster.Rows.Count).End(xlUp).Row + 1    ' gets next availablerow on the 'Master' Worksheet
        End If
     
        'Path and filename
        'Prompting user to choose the required folder
        MsgBox "Please select a folder with files to consolidate"
        Do
            With Application.FileDialog(msoFileDialogFolderPicker)
                .AllowMultiSelect = False
                .Show
                If .SelectedItems.Count > 0 Then
                    fPath = .SelectedItems(1) & "\"
                    Exit Do
                Else
                    If MsgBox("No folder chosen, do you wish to exit Macro?", _
                              vbYesNo) = vbYes Then Exit Sub
                End If
            End With
        Loop
     
        fPathDone = fPath & "Imported\"    'remember final \ in this string
        
        On Error Resume Next
        MkDir fPathDone    'creates the completed folder if missing
        On Error GoTo 0
        
        fName = Dir(fPath & "*.csv*")    'listing of desired files
    
        'Import a sheet from found files
        Do While Len(fName) > 0
        
            If fName <> ThisWorkbook.Name Then    'don't reopen this file accidentally
     
                Set wbData = Workbooks.Open(fPath & fName)    'Open file
     
                Set wsSrc = wbData.Worksheets(1) ' assume only one worksheet in CSV file
    
                'This is the section to customize, replace with your own action code as needed
                wsSrc.Columns("F:I").ClearContents
    
                With wsSrc
     
                    .Columns("A:E").AutoFilter
    
                    'Filter Latitude
                    .Range("$A$1:$E$31001").AutoFilter Field:=3, Criteria1:= _
                                                       ">=-37.9382", Operator:=xlAnd, Criteria2:="<=-32.004"
    
                    'Filter Longitude
                    .Range("$A$1:$E$31001").AutoFilter Field:=4, Criteria1:= _
                                                                  ">=136.7754", Operator:=xlAnd, Criteria2:="<=141.0698"
     
                    .Range("A:E").Copy wsMaster.Range("A" & NR)
                    
                End With
                
                wbData.Close False    'close file
     
                'Next row
    
                NR = wsMaster.Range("A" & wsMaster.Rows.Count).End(xlUp).Row + 1               Name fPath & fName As fPathDone & fName    'move file to IMPORTED folder
    
                fName = Dir    'ready next filename
            End If
        Loop
     
        wsMaster.Columns.AutoFit
        
        Application.DisplayAlerts = True    'turn system alerts back on
        Application.EnableEvents = True    'turn other macros back on
        Application.ScreenUpdating = True    'refreshes the screen
        
    End Sub
    If posting code please use code tags.

  10. #10
    New Member
    Join Date
    May 2011
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Subscript Out of range VBA Error

    How do i thank you enough?! thank you. this macro will save me hours!
    it works perfectly, i changed the filtering a bit to suit my needs, but thats all!

    However, i do not yet understand why you created a 'wsSrc' ?

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •