VBA Help

[mujim59]

I need some assistance with som VBA coding. I am making a sheet which tracks proficiency.

I have a master Jobs sheet which lists all people in Column A and all the jobs and the tasks associated with those jobs listed in the remaining columns.

At the top of the Proficiency sheet I have a combo box which lists all of the jobs. This is selected first. This loads the Job and all of the related tasks onto the proficiency sheet.

Down the first column I have several combo boxes that select the names of all of the personnel listed on the Master Jobs sheet.

The objective of the Proficiency sheet is to select the job, then select the personnel who worked the job and then enter the date under the task they accomplished. I have a send button. This is where I need help coding. When I press "send" I want all of the dates to be sent to the Master Jobs List for the personnel selected. Then I want to clear out all of the names and dates on my proficiency sheet.

I know my request is wordy. The nature of my work prevents me from being able to provide the work to look at.

Thank you,

mujim

 

[Andrew Poulsom]

In which columns on the Master Jobs List do you want to put the dates?

 

[mujim59]

I want the dates to go under the tasks on the Master Job Sheet associated with the name of the person. (I had to put the periods in to keep spacing correct.)


..................... Job 1 ................... Job 2..........................Job 3
..............Task 1.....Task 2.......Task 1...Task2............Task 1...Task 2
Name 1.....9/15.........................................................9/15

When I call up the job on the Proficiency sheet, I also call up the column number associated with the first column of the job located on the Master Job sheet. So I can step to the cell I need.

 

[Andrew Poulsom]

You can use the Match WorksheetFunction to find the row containing the person on your Master Job sheet. Example:

Sub Test()
    Dim Sh1 As Worksheet
    Dim Sh2 As Worksheet
    Dim Rng As Range
    Dim cell As Range
    Dim r As Long
    Set Sh1 = Worksheets("Master")
    Set Sh2 = Worksheets("Proficiency")
    With Sh2
        Set Rng = .Range("A3:A" & .Cells(.Rows.Count, 1).End(xlUp).Row)
    End With
    For Each cell In Rng
        r = WorksheetFunction.Match(cell.Value, Sh1.Range("A:A"), False)
        MsgBox r
    Next cell
End Sub

 

[mujim59]

The match worksheet doesn't work.
On the master job sheet, I have all of the jobs and tasks.

On the proficiency sheet, I only call up the job and tasks selected from the combo box.

When I select several names and update the proficiency dates, I want to send these dates to the relative area on the master job sheet.

Thank you,

 

[Andrew Poulsom]

You said:

"When I call up the job on the Proficiency sheet, I also call up the column number associated with the first column of the job located on the Master Job sheet. So I can step to the cell I need."

So I assumed you need only to know the row number of the cell containing the employee.

Loop around the employee cells, find the row number in master and copy the times to the relevant columns on that row.

 

[mujim59]

I'll try that, thanks,