Forum Discussion

le00p's avatar
le00p
Copper Contributor
Oct 26, 2023

Seeking Assistance for Developing a VBA Tool in Excel

Dear Community,

I am currently working on a project where I aim to develop a custom VBA tool in Excel to automate certain tasks. 

The primary objectives of my VBA tool are:

  1. Find the earliest possible date when an employee and a machine are available for scheduling a project.

  2. Mark the selected employee and machine, along with the earliest available date, in a specific format.

  3. Display the date in cell D1 as a result of the search.

My ultimate goal with this request is to find someone who can assist me in writing the VBA code for the macro that accomplishes these tasks.

The tool is structured as follows:

  • On the left side, there is an input form containing the following parameters:

    • The project duration in days, which is entered in cell D2.
    • Selection of employees in column A by marking them with a "x" for example.
    • Selection of machines in column C where the project can be implemented.

  • On the right side, there is a table with multiple columns. Each employee and machine has its own respective column. Column F contains the date column.

 

I am open to guidance, examples, resources, or references to existing posts within this community that can assist me in this task.

Thank you in advance for your support. Any contributions or responses will be greatly appreciated.

Best regards,

Leo

excel
Macros and VBA

1 Reply

  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor
    Oct 27, 2023

    le00p 

    Maybe it will help you further in your project. 

    I recommend creating a backup of your file before running the code.

    Sub FindEarliestDate()
    Dim ProjectDuration As Long
    Dim AvailableDate As Date
    Dim Employee As Range
    Dim Machine As Range
    Dim DateCell As Range
    
    ' Get project duration from cell D2
    ProjectDuration = Range("D2").Value
    
    ' Loop through table to find earliest available date
    For Each Employee In Range("A2:A100") ' Adjust the range as needed
        If Employee.Value = "x" Then ' Check if employee is selected
            For Each Machine In Range("C2:C100") ' Adjust the range as needed
                If Machine.Value <> "" Then ' Check if machine is selected
                    For Each DateCell In Employee.Offset(0, 4).Resize(1, ProjectDuration).Cells ' Adjust the offset and resize as needed
                        If DateCell.Value = "" Then
                            ' Found an available date
                            AvailableDate = DateCell.Value
                            Exit Sub
                        End If
                    Next DateCell
                End If
            Next Machine
        End If
    Next Employee
    
    ' Display the earliest available date in cell D1
    Range("D1").Value = AvailableDate
End Sub

     

Resources