VBA userform data transposition: HELP!

[rmcanada]

Hi there, I am stuck developing a VBA userform thats supposed to serve a dual purpose, depending on the information entered:

1) update an employee resource list with monthly data on '% of time logged' per project
2) add new entries for employees who are either new, or have just begun working on a new project.

The major problem I'm having is dynamically transposing the data from the userform to the 'resourcelist' tab. I can't seem to comprehend a formula that can search if an employee has worked on specified project before, and from there add the time logged to the column of the month specified. Furthermore, if they have not worked on the project, a new entry must be created with the first time logged in that specified month as well.

Perhaps a visual-aid may help with understanding my problem (seen below). Say for instance, the user enters (in their respecive fields) 'homer, working 0.4, on project 2 for m3,' the form aims to create a new entry for homer with appropriate time logged in m3.

It's very complex, I am aware. It should be noted that I am new to VBA as well, but I've picked up some fundamentals quite easily.

The commands are as follows:
nametextbox
projectcombobox
month[1,2,...,n]combobox
timeloggedtextbox

I would show my VBA code thus far but I doubt the proper solution is remotely close to an extension of what I've produced.

Any help is greatly appreciated!

Ryan

Name	Project	m1	m2	m3	m4	m5
bart	project 1	0.5	0.4
bart	project 2	0.4	0.4
homer	project 1	0.3	0.3

<TBODY>
</TBODY>

 

[dmt32]

Hi & welcome to the board.
It's always worth sharing what you have attempted as gives board better understanding of what you are trying to do.

From your post I think the answer to what you want to do can be mainly found in the VBA helpfile (FindNext Method)

See if following update to the sample helpfile code goes in right direction for you.

Place on either on your forms code page or in a standard module.

Sub UpdateResource(ByVal StaffName As String, ByVal Project As String, ByVal sMonth As Integer, ByVal TimeLogged As Double)
    Dim Lastrow As Long
    Dim msg As String, firstAddress As String


    msg = "  Name: " & StaffName & Chr(10) & _
          "Project: " & Project & Chr(10) & _
        " Month: " & sMonth & Chr(10) & Chr(10)


    With Worksheets(1)
        Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
        With .Columns(1)
            Set c = .Find(StaffName, LookIn:=xlValues)
            If Not c Is Nothing Then
                firstAddress = c.Address
                Do
                    If c.Offset(0, 1).Value = Project Then
                        c.Offset(0, 1 + sMonth).Value = TimeLogged
                        MsgBox msg & "Record Updated", 48, "Record Updated"
                        Exit Sub
                    End If


                    Set c = .FindNext(c)
                Loop While Not c Is Nothing And c.Address <> firstAddress
            End If
        End With
        'new record
        .Cells(Lastrow, 1).Value = StaffName
        .Cells(Lastrow, 2).Value = Project
        .Cells(Lastrow, 2).Offset(0, sMonth).Value = TimeLogged


        MsgBox msg & "New Record Entered", 48, "New Record"


    End With
End Sub

You will need to change the worksheet index (shown in RED) if your data is not on the the first sheet or you can add the sheets name.




From your userform commandbutton call it like this:

Private Sub CommandButton1_Click()
UpdateResource Me.nametextbox.Text, Me.projectcombobox.Text, CInt(Me.MonthComboBox.Text),CDbl(Me.timeloggedtextbox.Text)
End Sub

I have assumed that the MonthComboBox contains numeric values only i.e 1 to 12, if not, code will need to be adjusted if you are using it's listindex property.

Hope Helpful

Dave