VBA Help

mujim59

New Member
Joined
Nov 25, 2002
Messages
25
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
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

mujim59

New Member
Joined
Nov 25, 2002
Messages
25
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
You can use the Match WorksheetFunction to find the row containing the person on your Master Job sheet. Example:

Code:
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

New Member
Joined
Nov 25, 2002
Messages
25

ADVERTISEMENT

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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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.
 

Forum statistics

Threads
1,136,272
Messages
5,674,756
Members
419,525
Latest member
helensesc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top