Subscript out of range in VBA macro

Martunis99

New Member
Joined
Aug 16, 2021
Messages
19
Office Version
  1. 2016
Platform
  1. Windows
Hi guys,

Edit: Reposting because I forgot to format my code and made a mistake in previous question.

I am creating a macro that checks if two fields have the same value across two sheets, and if so an offseted cell from one of those tabs is passed onto a variable and later used to open some files.
However, I don't want to open any files (not even run the code) when this offseted cell is blank. Even though I tried to place some validation testing (to test if the variable is empty) the code still executes and returns an error when opening the file (which I assume is caused by the fact that the variable with the file name is either empty or null). Could you help me out? What am I doing wrong?

Here is my code. The error is in the line highlighted in bold:

(code pasted just for context)

VBA Code:
Set MyObject = CreateObject("Scripting.FileSystemObject")
Set Folder = MyObject.GetFolder("\\mercer.com\uk_data\Eworking\RSC\BEL\Netherlands\" + Worksheets("Client specific").Cells(5, "B").Value + "\")
Set SubFolders = Folder.SubFolders

For Each SubFolders In SubFolders
If InStr(SubFolders.Name, ValYear) Then
RRPath = SubFolders.Path + "\03-Liabilities\01-ReplicationRun\"
BaselinePath = SubFolders.Path + "\03-Liabilities\02-Baseline\"
LiabPath = SubFolders.Path + "\03-Liabilities\04-OngoingBasis\"
Exit For
End If
Next

(actual relevant part)

Rich (BB code):
Set RowBasis = ThisWorkbook.Worksheets("Basis").Range("B16:Z16")
Set RowIO = ThisWorkbook.Worksheets("Ind Output").Range("A1:KU1")

For Each Cell In RowBasis.Cells
StudioNode = Cell.Offset(2, 0).Value
For Each CellIO In RowIO.Cells
If (Cell = CellIO) And (Not IsEmpty(StudioNode)) Then

If InStr(StudioNode, "Replication") Or InStr(StudioNode, "RR") Then
Set File = MyObject.GetFolder(RRPath)
ElseIf InStr(StudioNode, "Baseline") Then
Set File = MyObject.GetFolder(BaselinePath)
Else
Set File = MyObject.GetFolder(LiabPath)
End If

For Each File In File.Files
Workbooks.Open File
Workbooks(StudioNode & ".xlsx").Worksheets("Sheet1").Columns("A").Select --> Subscript out of range error

With Selection
.NumberFormat = "General"
.Value = .Value
End With

Workbooks(StudioNode & ".xlsx").Worksheets("Sheet1").Range("A7", Range("I7").End(xlDown)).Copy
ThisWorkbook.Worksheets("Ind Output").Range(CellIO.Offset(2, -1).Address(0, 0)).PasteSpecial Paste:=xlPasteValues

Application.CutCopyMode = False
Workbooks(StudioNode & ".xlsx").Close (False)
Next File

End If
Next CellIO
Next Cell

Thank you so much!!
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
This same error occurs when there's a reference to a spreadsheet that does not exist.
 
Upvote 0
Use better names for variables and try not to use keywords and the sort. Getfolder should return a folder object, yet you are setting it to variable file. Makes it hard to follow. Explicitly dim your variables, better use Option Explicit at the top of modules. Will be easier to debug. Debug.print or watch window also helps.
 
Upvote 0
The workbook which was just opened according to these lines of code
VBA Code:
For Each File In File.Files
Workbooks.Open File
will be explicitly the active workbook.
If the Workbooks(StudioNode & ".xlsx") workbook is another one than the above, it's not active. You only can select Ranges on a active workbook/worksheet.

In general, it would be better to make references to workbooks and worksheets like you did with some ranges.
 
Upvote 0
@GWteB Thank you for the reply! I close the file after I am done copying the ranges, so each time a new workbook is opened, the workbook in which I am selecting ranges is always the same in both those instances. But I see what you mean makes sense.

@Special-K99 yeah, I also thought about that. But the spreadsheet exists. I think the issue is with the variable StudioNode. After the last file is copied to my spreadsheet, and the loop continues to check if the offseted variable is not empty, it seems that even though the field is empty the code is still executed. When I debug the StudioNode variable is often assigned the value "NONE" or "". So it is effectively trying to select a range in a file that doesnt exist. Even after introducing some validations it is still not working.

@*Gokhan Aycan Thank you for your input! I am still quite new, but I will be sure to name my variables and reference them more explicitly.
 
Upvote 0
Does z18 has a filename entered? Since you are assigning cell value to a variable, variable can be not empty hence invalidate the isempty check.
 
Upvote 0
Try using cell.value = "" without assigning first as a troubleshooting steps.
 
Upvote 0
Solution
@Gokhan Aycan you absolute genius. It finally worked. This is how I adapted the code above

VBA Code:
For Each Cell In RowBasis.Cells
    If Cell.Offset(2, 0).Value = "" Then
        StudioNode = ""
    Else
        StudioNode = Cell.Offset(2, 0).Value
    End If
    For Each CellIO In RowIO.Cells
        If (Cell = CellIO) And (Not StudioNode = "") Then

Worked like a charm. The macro copied the data from the last file and then terminated with no errors. Thank you my friend! Much appreciated!
 
Upvote 0

Forum statistics

Threads
1,223,236
Messages
6,170,912
Members
452,366
Latest member
TePunaBloke

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
Back
Top