How to Structure Conditional Statement for an Array

[Slicemahn]

Good day VBAExpress Nation!

I am challenged to find a way to set up a conditional statement for an array. More specifically, I want to test if an item is in the array, if it matches perform a copy operation to a new location, if doesn't match then move to the next file. My code is below:


Sub OpenFolder()Dim FileName As String
Dim fPath As String
Dim fDtName As String
Dim i As Long
Dim j As Long
Dim dbTarget As String
dbRosterFolder = ActiveWorkbook.Sheets("Production Dbs").Range("D1").CurrentRegion.Columns(4).Value
fDtName = Format(Now, "yyyy-mm-dd ")
' Loop through Db Roster locations
For i = 2 To UBound(dbRosterFolder)
' placeholder
Dim dbDirectoryContents() As String
ReDim dbDirectoryContents(1000)
dbTarget = Dir$(Cells(i, 4).Value)
Do While dbTarget <> ""
dbDirectoryContents(Counter) = dbTarget
dbTarget = Dir$
Counter = Counter + 1
Loop
Next i
End Sub

[Bob Phillips]

You don't say what the match criteria are, but does this move you further forward


Sub OpenFolder()
Dim FileName As String
Dim fPath As String
Dim fDtName As String
Dim i As Long
Dim j As Long
Dim dbSource As String
Dim dbTarget As String

dbRosterFolder = ActiveWorkbook.Worksheets("Production Dbs").Range(.Range("D1").End(xlDown), .Range("D1").End(xlToRight))

fDtName = Format(Now, "yyyy-mm-dd ")

' Loop through Db Roster locations

For i = 2 To UBound(dbRosterFolder, 1)

' placeholder
Dim dbDirectoryContents() As String
ReDim dbDirectoryContents(1000)

For j = 3 To UBound(dbRosterFolder, 2)

If Not IsEmpty(dbRosterFolder(i, j).Value) Then

dbSource = Dir$(dbRosterFolder(i, 1) & dbRosterFolder(i, j))
dbTarget = Dir$(dbRosterFolder(i, 2) & dbRosterFolder(i, j))

'do what you need to do with workbook and if meets your criteria

Name dbSource As dbTarget
End If
Next j
Next i
End Sub

[snb]

I fear this will do what you are after:


Sub M_snb()
sn = Sheet1.Cells(1).CurrentRegion

For j = 2 To UBound(sn)
For jj = 5 To 7
If sn(j, jj) <> "" And Dir(sn(j, 4) & sn(j, jj)) <> "" Then Name sn(j, 4) & sn(j, jj) As sn(j, 5) & sn(j,jj)
Next
Next
End Sub