Martunis99
New Member
- Joined
- Aug 16, 2021
- Messages
- 19
- Office Version
- 2016
- Platform
- 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)
(actual relevant part)
Thank you so much!!
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!!