VBA two inputs to return a result

[cthorn112]

Hello,

I'm writing an invoice program to be used in excel. I have some VBA experience and have come up with the code below. The goal is to automate this process as much as possible. My vision is to have the user input the name and date(s) and have VBA/excel take care of the rest. I have a blank invoice template. The user inputs the name which is populated into A6 and B3. I have a two vlookup's return code and desc from promoters worksheet associated with the person's name. After the user inputs the date I have that populate B16.
My first issue is how to return the correct comp and red value from the MASTER worksheet. I need to capture the name and input_date in order to do this. I looked around and found some ideas (vlookup/vlookup, vlookup/hlookup, vlookup/index/match, vlookup/find). My theory is to do a vlookup that will find the name in col A and to reference the input_date to match the dates in row 1 then bring back the comp and red value associated with the name. For example If the user types in 'Daniel Gonzalez' for name and '4/01/2011' for input_date, it will return 5 for comp and 0 for red (these are the values for Daniel Gonzalez under the date 4/01/2011, I have multiple date columns in MASTER worksheet like 3/18/2011, 3/19/2011, 3/25/2011, 3/26/2011, 4/01/2011, etc.).
My other issue is if the user wants to input another date. I created a pop-up which asks the user if they have more dates to enter (case statement). I want to allow them to enter up to 6 different dates. I want to know how to return back to the pop-up part of the code and ask them if they have more dates to enter, rather then write the same code over and over again.
My last issue is retrieving the last date the user inputs and populating Job Completed (B12) with that value. For example if the user types in 3 dates, I want the last date the user entered to be in B12, not the first or second date they entered.

If you see a better/simpler approach please let me know.
Many thanks in advanced for reading/helping.

-Chris


VBA Code

Sub EnterName()

    Dim name As String
    Dim todays_date As Date
    Dim payment_due As Date
    Dim job_completed As Date
    Dim input_date As Date
    Dim code As Variant
    Dim desc As Variant
    Dim more_dates As String
    Dim btns As Integer
    Dim choice As Integer
    Dim comp As String
    Dim red As String
    
    name = InputBox("Please enter name", "Name entered")
    Sheets("PROMOTER_TEMPLATE2").Range("A6,B3").Value = name
    
    code = Application.WorksheetFunction.VLookup(name, Worksheets("promoters").Range("A:C"), 3, False)
    
    desc = Application.WorksheetFunction.VLookup(name, Worksheets("promoters").Range("A:B"), 2, False)
    
    If IsError(code) Then ' returns code for name
    MsgBox "match not found"
    Else
    Sheets("PROMOTER_TEMPLATE2").Range("A16").Value = code
    End If
    
    If IsError(desc) Then ' returns desc for name
    MsgBox "match not found"
    Else
    Sheets("PROMOTER_TEMPLATE2").Range("C16").Value = desc ' MsgBox "match found" or "match found at pos: " & res
    End If
    
    todays_date = DateValue(Now) ' todays date
    Sheets("PROMOTER_TEMPLATE2").Range("D6").Value = todays_date
    
    payment_due = DateValue(Now + 7) ' payment due is today's date + 7
    Sheets("PROMOTER_TEMPLATE2").Range("A12").Value = payment_due
    
     input_date = InputBox("Please enter date", "Date entered") ' input_date  is used for looking up the dates in master worksheet.
    Sheets("PROMOTER_TEMPLATE2").Range("B16").Value = input_date
    
     job_completed = (input_date - 3) ' this should return the last input  date that the user types in, right now its just taking the first  input_date - 3
    Sheets("PROMOTER_TEMPLATE2").Range("B12").Value = job_completed
    
     If IsError(comp) Then ' not in use yet, need the name and input_date to  be used to return the correct comp value from master worksheet
    MsgBox "match not found"
    Else
    comp = Application.WorksheetFunction.VLookup(name, Worksheets("MASTER").Range("A:B"), 2, False)
    Sheets("PROMOTER_TEMPLATE2").Range("D16").Value = comp
    End If
    
     If IsError(red) Then ' not in use yet need the name and input_date to  be used to return the correct red value from master worksheet
    MsgBox "match not found"
    Else
    red = Application.WorksheetFunction.VLookup(name, Worksheets("MASTER").Range("A:C"), 3, False)
    Sheets("PROMOTER_TEMPLATE2").Range("E16").Value = red
    End If
    
    more_dates = "Do you have more dates to enter ?" ' if the user has more dates to enter then enter the dates.
    btns = vbYesNo + vbmore_dates + vbDefaultButton2
    choice = MsgBox(more_dates, btns) ' MsgBox choice 7 = no 6 = yes
    
    Select Case choice
        Case 6
            MsgBox "Please enter the next date"
            input_date = InputBox("Please Enter Date", "Date Entered")
            Sheets("PROMOTER_TEMPLATE2").Range("B17").Value = input_date
            desc = Application.WorksheetFunction.VLookup(name, Worksheets("promoters").Range("A:B"), 2, False)
            Sheets("PROMOTER_TEMPLATE2").Range("C17").Value = desc
            ' comp = Application.WorksheetFunction.VLookup(name, Worksheets("MASTER").Range("A:B"), 2, False)
            ' Sheets("PROMOTER_TEMPLATE2").Range("D17").Value = comp
            ' red = Application.WorksheetFunction.VLookup(name, Worksheets("MASTER").Range("A:C"), 3, False)
            ' Sheets("PROMOTER_TEMPLATE2").Range("E17").Value = red
        
        Case 7
            MsgBox "You are done"
    End Select
End Sub

MASTER worksheet

<table width="1209" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="mso-width-source:userset;mso-width-alt:5412;width:111pt" width="148"> <col style="mso-width-source:userset;mso-width-alt:5705;width:117pt" width="156"> <col style="mso-width-source:userset;mso-width-alt:2084;width:43pt" width="57"> <col style="mso-width-source:userset;mso-width-alt:1572;width:32pt" width="43"> <col style="mso-width-source:userset;mso-width-alt:2962;width:61pt" width="81"> <col style="mso-width-source:userset;mso-width-alt:2084;width:43pt" width="57"> <col style="mso-width-source:userset;mso-width-alt:1572;width:32pt" width="43"> <col style="mso-width-source:userset;mso-width-alt:2962;width:61pt" width="81"> <col style="mso-width-source:userset;mso-width-alt:2084;width:43pt" width="57"> <col style="mso-width-source:userset;mso-width-alt:1572;width:32pt" width="43"> <col style="mso-width-source:userset;mso-width-alt:2962;width:61pt" width="81"> <col style="mso-width-source:userset;mso-width-alt:2084;width:43pt" width="57"> <col style="mso-width-source:userset;mso-width-alt:1572;width:32pt" width="43"> <col style="mso-width-source:userset;mso-width-alt:2962;width:61pt" width="81"> <col style="mso-width-source:userset;mso-width-alt:2084;width:43pt" width="57"> <col style="mso-width-source:userset;mso-width-alt:1572;width:32pt" width="43"> <col style="mso-width-source:userset;mso-width-alt:2962;width:61pt" width="81"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td class="xl73" style="height:15.0pt;width:111pt" width="148" height="20">
</td> <td class="xl73" style="width:117pt" width="156">
</td> <td colspan="3" class="xl72" style="width:136pt" width="181">3.18.2011</td> <td colspan="3" class="xl72" style="width:136pt" width="181">3.19.2011</td> <td colspan="3" class="xl72" style="width:136pt" width="181">3.25.2011</td> <td colspan="3" class="xl72" style="width:136pt" width="181">3.26.2011</td> <td colspan="3" class="xl71" style="width:136pt" width="181">4.01.2011</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl74" style="height:15.0pt" height="20">Promoter Name</td> <td class="xl74" style="border-left:none">Promoter List</td> <td class="xl67" style="border-left:none">COMP</td> <td class="xl67" style="border-left:none">RED</td> <td class="xl68" style="border-left:none">Payment</td> <td class="xl67" style="border-left:none">COMP</td> <td class="xl67" style="border-left:none">RED</td> <td class="xl68" style="border-left:none">Payment</td> <td class="xl67" style="border-left:none">COMP</td> <td class="xl67" style="border-left:none">RED</td> <td class="xl68" style="border-left:none">Payment</td> <td class="xl67" style="border-left:none">COMP</td> <td class="xl67" style="border-left:none">RED</td> <td class="xl68" style="border-left:none">Payment</td> <td class="xl67" style="border-top:none;border-left:none">COMP</td> <td class="xl67" style="border-top:none;border-left:none">RED</td> <td class="xl68" style="border-top:none;border-left:none">Payment</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl75" style="height:15.0pt;border-top:none" height="20">Daniel Gonzalez</td> <td class="xl76" style="border-top:none;border-left:none">A&D</td> <td class="xl69" style="border-top:none;border-left:none" align="right">0</td> <td class="xl69" style="border-top:none;border-left:none" align="right">0</td> <td class="xl70" style="border-top:none;border-left:none" align="right">0</td> <td class="xl69" style="border-top:none;border-left:none" align="right">0</td> <td class="xl69" style="border-top:none;border-left:none" align="right">3</td> <td class="xl70" style="border-top:none;border-left:none" align="right">15</td> <td class="xl69" style="border-top:none;border-left:none" align="right">0</td> <td class="xl69" style="border-top:none;border-left:none" align="right">0</td> <td class="xl70" style="border-top:none;border-left:none" align="right">0</td> <td class="xl69" style="border-top:none;border-left:none" align="right">0</td> <td class="xl69" style="border-top:none;border-left:none" align="right">0</td> <td class="xl70" style="border-top:none;border-left:none" align="right">0</td> <td class="xl69" style="border-top:none;border-left:none" align="right">5</td> <td class="xl69" style="border-top:none;border-left:none" align="right">0</td> <td class="xl70" style="border-top:none;border-left:none" align="right">0</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl75" style="height:15.0pt;border-top:none" height="20">(manully fill)</td> <td class="xl76" style="border-top:none;border-left:none">Abe</td> <td class="xl69" style="border-top:none;border-left:none" align="right">0</td> <td class="xl69" style="border-top:none;border-left:none" align="right">0</td> <td class="xl70" style="border-top:none;border-left:none" align="right">0</td> <td class="xl69" style="border-top:none;border-left:none" align="right">0</td> <td class="xl69" style="border-top:none;border-left:none" align="right">0</td> <td class="xl70" style="border-top:none;border-left:none" align="right">0</td> <td class="xl69" style="border-top:none;border-left:none" align="right">0</td> <td class="xl69" style="border-top:none;border-left:none" align="right">0</td> <td class="xl70" style="border-top:none;border-left:none" align="right">0</td> <td class="xl69" style="border-top:none;border-left:none" align="right">0</td> <td class="xl69" style="border-top:none;border-left:none" align="right">0</td> <td class="xl70" style="border-top:none;border-left:none" align="right">0</td> <td class="xl69" style="border-top:none;border-left:none" align="right">0</td> <td class="xl69" style="border-top:none;border-left:none" align="right">0</td> <td class="xl70" style="border-top:none;border-left:none" align="right">0</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl75" style="height:15.0pt;border-top:none" height="20">Gil Dery</td> <td class="xl77" style="border-top:none;border-left:none">Absolut</td> <td class="xl69" style="border-top:none;border-left:none" align="right">0</td> <td class="xl69" style="border-top:none;border-left:none" align="right">0</td> <td class="xl70" style="border-top:none;border-left:none" align="right">0</td> <td class="xl69" style="border-top:none;border-left:none" align="right">0</td> <td class="xl69" style="border-top:none;border-left:none" align="right">0</td> <td class="xl70" style="border-top:none;border-left:none" align="right">0</td> <td class="xl69" style="border-top:none;border-left:none" align="right">0</td> <td class="xl69" style="border-top:none;border-left:none" align="right">0</td> <td class="xl70" style="border-top:none;border-left:none" align="right">0</td> <td class="xl69" style="border-top:none;border-left:none" align="right">0</td> <td class="xl69" style="border-top:none;border-left:none" align="right">0</td> <td class="xl70" style="border-top:none;border-left:none" align="right">0</td> <td class="xl69" style="border-top:none;border-left:none" align="right">0</td> <td class="xl69" style="border-top:none;border-left:none" align="right">0</td> <td class="xl70" style="border-top:none;border-left:none" align="right">0</td> </tr> </tbody></table>

 

[cthorn112]

60 something views and no replies ? Did I miss something or do something wrong ?