+ Reply to Thread
Results 1 to 3 of 3

How to add new data to existing cell based on multiple selective inputs?

Hybrid View

  1. #1
    Registered User
    Join Date
    04-01-2009
    Location
    Houston, TX
    MS-Off Ver
    Excel 2003
    Posts
    2

    How to add new data to existing cell based on multiple selective inputs?

    I know what I want to do, just not how to do it or describe it, so please forgive me if this doesn’t make any sense.

    I have a spreadsheet of courses required to reach a certification. On this spreadsheet I have listed the number of hours required for each course in one column, and how many hours I have accrued in an adjoining column. Not all the hours will occur at once, so I tend to bound from cell to cell adding hours in small amounts. What I am trying to do is create a macro that will allow me to add to the existing number of hours to the newly accrued hours, without typing over what is already there.

    For example…Class 1 requires five hours total, and I have two hours accrued. If I accrue two more hours (for a total of four hours) I want to update cell E2 without going in to this cell manually and changing this number. I would like to enter the additional two hours in a text box or similar function, and have that function update E2.

    To add to the level of difficulty, there are four levels of class. This means not only do I need to be able to select which class hours need updated, but which level of class.

    I have attached the spreadsheet I am working with to try to make things a little clearer. I hope.
    Attached Files Attached Files
    Last edited by AlphaSvn; 04-01-2009 at 06:09 PM.

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: How to add new data to existing cell based on multiple selective inputs?

    Maybe like this with a command button instead of comboboxes.
    Private Sub CommandButton1_Click()
    Dim Course As Range, Level As Range, Hours As Integer, r As Long
    
    Set Class = Range("B2")
    Set Level = Range("B3")
    Hours = Range("B4").Value
    
    If IsEmpty(Range("B2")) Then
        MsgBox "Please select a Class"
        Exit Sub
    ElseIf IsEmpty(Range("B3")) Then
        MsgBox "Please select a course Level"
        Exit Sub
    ElseIf IsEmpty(Range("B4")) Then
        MsgBox "Please enter number of Hours earned"
        Exit Sub
    Else
        r = Application.Match(Class, Columns(4))
        Select Case Level.Value
            Case "Intro"
                Range("E" & r).Value = Range("E" & r).Value + Hours
            Case "Basic"
                Range("H" & r).Value = Range("H" & r).Value + Hours
            Case "Intermediate"
                Range("K" & r).Value = Range("K" & r).Value + Hours
            Case "Advanced"
                Range("N" & r).Value = Range("N" & r).Value + Hours
            Case Else
        End Select
    End If
    Range("B2:B4").ClearContents
    Range("B2").Select
    End Sub
    Attached Files Attached Files
    Last edited by JBeaucaire; 04-01-2009 at 05:11 PM.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    04-01-2009
    Location
    Houston, TX
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: How to add new data to existing cell based on multiple selective inputs?

    Wow, that is exactly what I wanted! And quick too! Thanks.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1