trinidad2011
New Member
- Joined
- May 8, 2011
- Messages
- 13
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 Sub
I will be delighted if someone can find the error or kindly explain what the error means
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:
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 Sub
I will be delighted if someone can find the error or kindly explain what the error means
