Subscript out of range in VBA macro

[Martunis99]

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)

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)

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!!

 

[Special-K99]

This same error occurs when there's a reference to a spreadsheet that does not exist.

 

[Gokhan Aycan]

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.

 

[GWteB]

The workbook which was just opened according to these lines of 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.

 

[Martunis99]

@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.

 

[Gokhan Aycan]

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.

 

[Martunis99]

@Gokhan Aycan I can confirm cell Z18 is completely empty. That is a good point. How can I workaround this? Does it get null or maybe 0?

 

[Gokhan Aycan]

Try using cell.value = "" without assigning first as a troubleshooting steps.

 

[Martunis99]

@Gokhan Aycan you absolute genius. It finally worked. This is how I adapted the code above

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!