VBA code that references another directory if one does not work

[willow1985]

I have a spreadsheet that contains a macro that when run opens various other spreadsheets, grabs their data and closes them. However this main spreadsheet is accessed by multiple users in 2 different ways on a network. Sometimes the drive can appear as X and other times as an address like 190.165.1.22\

Basically what I am looking for is if the macro runs into an error where it cannot find the drive (I am assuming this is the problem because I am getting a Subscript out of range Error) that it will try the 2nd directory (see VBA code)

Any help would be greatly appreciated!

Dim CAPAD As String

Dim CCD As String

Dim CID As String

Dim NCRD As String

Dim ASD As String

Dim CAPAN As String

Dim CCN As String

Dim CIN As String

Dim NCRN As String

Dim ASN As String



CAPAD = Workbooks("Quality Dashboard").Sheets("Graph Data").Range("B15").Value ‘ or Workbooks("Quality Dashboard").Sheets("Graph Data").Range("B22").Value

CCD = Workbooks("Quality Dashboard").Sheets("Graph Data").Range("B16").Value ‘or Workbooks("Quality Dashboard").Sheets("Graph Data").Range("B23").Value

CID = Workbooks("Quality Dashboard").Sheets("Graph Data").Range("B17").Value ‘or Workbooks("Quality Dashboard").Sheets("Graph Data").Range("B24").Value

NCRD = Workbooks("Quality Dashboard").Sheets("Graph Data").Range("B18").Value ‘or Workbooks("Quality Dashboard").Sheets("Graph Data").Range("B25").Value

ASD = Workbooks("Quality Dashboard").Sheets("Graph Data").Range("B19").Value ‘or Workbooks("Quality Dashboard").Sheets("Graph Data").Range("B26").Value



CAPAN = Workbooks("Quality Dashboard").Sheets("Graph Data").Range("B29").Value

CCN = Workbooks("Quality Dashboard").Sheets("Graph Data").Range("B30").Value

CIN = Workbooks("Quality Dashboard").Sheets("Graph Data").Range("B31").Value

NCRN = Workbooks("Quality Dashboard").Sheets("Graph Data").Range("B32").Value

ASN = Workbooks("Quality Dashboard").Sheets("Graph Data").Range("B33").Value

Quality Dashboard.xlsm
	A	B
14	Directory 1
15	CAPA Directory	\\190.165.1.22\cad\QUALITY\Group\Corrective Actions\CAPA findings\CAPA Log.xlsm
16	CC Directory	\\190.165.1.22\cad\QUALITY\Group\Corrective Actions\Customer Concerns - Corrective Action\Customer Concern - Warranty Request Log.xlsm
17	CI Directory	\\190.165.1.22\com\Lean Folder\Continuous Improvement Database.xlsm
18	NCR Directory	\\190.165.1.22\cad\QUALITY\Group\Supplier Non-Conformance Reports\Supplier NCR Log.xlsm
19	Audit Schedule Directory	\\190.165.1.22\cad\QUALITY\Group\Audit\Internal Audit Schedule.xlsm
20
21	Directory 2
22	CAPA Directory	X:\QUALITY\Group\Corrective Actions\CAPA findings\CAPA Log.xlsm
23	CC Directory	X:\QUALITY\Group\Corrective Actions\Customer Concerns - Corrective Action\Customer Concern - Warranty Request Log.xlsm
24	CI Directory	S:\Lean Folder\Continuous Improvement Database.xlsm
25	NCR Directory	X:\QUALITY\Group\Supplier Non-Conformance Reports\Supplier NCR Log.xlsm
26	Audit Schedule Directory	X:\QUALITY\Group\Audit\Internal Audit Schedule.xlsm
27
28	File Names
29	CAPA	CAPA Log.xlsm
30	CC	Customer Concern - Warranty Request Log.xlsm
31	CI	Continuous Improvement Database.xlsm
32	NCR	Supplier NCR Log.xlsm
33	Audit Schedule	Internal Audit Schedule.xlsm
Graph Data

Cell Formulas
Range	Formula
B15	B15	="\\190.165.1.22\cad\QUALITY\Group\Corrective Actions\CAPA findings\"&B29
B16	B16	="\\190.165.1.22\cad\QUALITY\Group\Corrective Actions\Customer Concerns - Corrective Action\"&B30
B17	B17	="\\190.165.1.22\com\Lean Folder\"&B31
B18	B18	="\\190.165.1.22\cad\QUALITY\Group\Supplier Non-Conformance Reports\"&B32
B19	B19	="\\190.165.1.22\cad\QUALITY\Group\Audit\"&B33
B22	B22	="X:\QUALITY\Group\Corrective Actions\CAPA findings\"&B29
B23	B23	="X:\QUALITY\Group\Corrective Actions\Customer Concerns - Corrective Action\"&B30
B24	B24	="S:\Lean Folder\"&B31
B25	B25	="X:\QUALITY\Group\Supplier Non-Conformance Reports\"&B32
B26	B26	="X:\QUALITY\Group\Audit\"&B33

 

[Tupe77]

Nice to hear that we managed to help and thanks for the feedback.