Excel VBA Searching Unique Variables to Match against Exported Schedule Files to Determine Date

[Bale626]

VBA Newbie here; I am attempting to use a sort of search function in a schedule that I create every day for work.

In essence, the schedule that our software exports does not include the date that the finished good jobs are tied to; the jobs are scheduled per week, but that data is not shown in the exported report. However, the compiled schedule that I put out needs to have the week the jobs are running tied to them.

What I am trying to figure out is how to have a macro use the "F/G job #" column (E) on the primary workbook to refer to the job number [Production schedule], then search across different workbooks to find which workbook the job number is located at [Production Schedule (1), Production Schedule (2), Production Schedule (3), etc.], and then spit out a formula that will refer to the specific week that job is to run, in the "Week to Run" column (Q).

Here is what I have so far, code-wise.

Sub Link_Job_To_Date()

    Dim folderPath As String
    Dim fileName As String
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim searchValues As Variant
    Dim found As Boolean
    Dim outputFormula As String
    Dim colToSearch As Long
    Dim cell As Range
    Dim matchCount As Long
    
  ' Set the folder path where the workbooks are located
    folderPath = "C:\Users\My_Name\Downloads\"
    
  ' Set the values you are searching for
    searchValues = Array("Value1", "Value2", "Value3") ' Add your values here
    
  ' Set the column number to search
    colToSearch = 5 ' Change to the column number you want to search
    
  ' Loop through each file in the folder
    fileName = Dir(folderPath & "*.xlsx")
    Do While fileName <> ""
        Set wb = Workbooks.Open(folderPath & fileName)
        found = False
        
  ' Loop through each sheet in the workbook
        For Each ws In wb.Sheets
            matchCount = 0
            
  ' Loop through each cell in the specified column
            For Each cell In ws.Columns(colToSearch).Cells
                If IsError(Application.Match(cell.Value, searchValues, 0)) = False Then
                    matchCount = matchCount + 1
                End If
            Next cell
            
  ' Check if all values are found in the column
            If matchCount = UBound(searchValues) + 1 Then
                found = True
                Exit For
            End If
        Next ws
        
  ' If found, set the output formula based on the file name
        If found Then
            outputFormula = "YourFormulaBasedOnFileName"
            Exit Do
        End If
        
        wb.Close SaveChanges:=False
        fileName = Dir
    Loop
    
  ' Output the formula to a specific cell in the active workbook
    If found Then
        ThisWorkbook.Sheets("Sheet1").Range("A1").Formula = outputFormula
    Else
        MsgBox "Values not found in any document."
    End If
End Sub

This is the closest that I have come to something that (might) work.

I am wanting to get the following outputs:

Production Schedule (1) --> =TODAY() + (13 - WEEKDAY(TODAY()))
Production Schedule (2) --> =TODAY() + (20 - WEEKDAY(TODAY()))
Production Schedule (3) --> =TODAY() + (27 - WEEKDAY(TODAY()))
not found at all --> =TODAY() + (6 - WEEKDAY(TODAY()))

the last row would then get the correct dates on the jobs running the current week.

 

[sijpie]

Some questions:

1. The Production Schedule (x) workbooks, do the contain more than one sheet?
2. the image you show, is that the Schedule sheet or a Production Schedule workbook?
3. Does the jobnumber appear in column E in both the Schedule and the PS workbook?

If I understand it correctly, the macro should do the following:

1. In the Schedule workbook go through the list of jobnumbers in column E
2. Check in each of the PS workbooks in column E (?) if the jobnumer is listed
3. Add a schedule date in column Q of the Schedule workbook depending on the name of the PS workbook where the jobnumber was found. (according to your formula's)

Let me know the answers to the above, and if my undersganding is correct