Need help with a complex document?

KristenB

New member
Joined
Jan 2, 2020
Messages
35
Reaction score
0
Points
0
Excel Version(s)
365
Hello,

I am creating a rather complex document and am needing some assistance. The document that I am creating is a travel reimbursement document for employees to submit to receive reimbursement for meals, mileage, etc.. This document includes a userform that the user completes for each day that they have traveled. On the userform they enter the date, times, project numbers, explanation and make some necessary selections. The information from my userform goes to a spreadsheet where the user can then see all entries and a total of what they should be reimbursed for the entire month.

Let me begin my explaining just the meal reimbursement piece to give a clearer explanation of what is entered and leading to what I am needing. The user enters the date and times of their travel, the information goes to the spreadsheet and the user then sees, via checkboxes, which meals they are eligible to receive (calculated on the spreadsheet). The user can remove any checkboxes if they do not wish to receive reimbursement for the meal and/or if they were provided a no-cost meal. When the user submits their form, the information goes to the spreadsheet and populates the amount they will be reimbursed for that day.

Here is what I need to do next, and I have written the following code as a start. I need my code, when the userform is submitted: If there is a value in the meal field on the spreadsheet, I need my code to determine the appropriate expense code, and then copy the meal value from the spreadsheet, the expense code it determines, and another field from the userform to a 'coding' spreadsheet. This additional spreadsheet will be used to create a pivot table as summary for data entry into our payables system. ( I want to be sure to note here that there will actually be at least three other similar processes that need to be moved to the same 'coding' spreadsheet with the same selection of the submit button but I need each of these to 'stack' below the other on the 'coding' spreadsheet.

Meal expense coding that I have so far just determining which expense code should be used is below (I have also attached the document for you to use in reference):

Code:
  '''MOVE MEAL VALUES WITH EXPENSE CODE TO EXPENSE CODE PROCESSING DATABASE'''If Sheets("Travel Expense Voucher").Range("D5") = "2" And Sheets("Travel Expense Voucher").Range("Meals") <> "" Then
    Sheets("Expense Code Processing").Range("C2") = ("62495")
    ElseIf Sheets("Travel Expense Voucher").Range("D5") = "1" And Sheets("Travel Expense Voucher").Range("Meals") <> "" And cmbOVNRTN = ("Return") And cmbInOutState = ("In-State") Then
        Sheets("Expense Code Processing").Range("C2") = "62407"
    ElseIf Sheets("Travel Expense Voucher").Range("D5") = "1" And Sheets("Travel Expense Voucher").Range("Meals") <> "" And cmbOVNRTN = ("Overnight") And cmbInOutState = ("In-State") Then
        Sheets("Expense Code Processing").Range("C2") = "62410"
    ElseIf Sheets("Travel Expense Voucher").Range("D5") = "1" And Sheets("Travel Expense Voucher").Range("Meals") <> "" And cmbOVNRTN = ("Return") And cmbInOutState = ("Out-of-State") Then
        Sheets("Expense Code Processing").Range("C2") = "62430"
    ElseIf Sheets("Travel Expense Voucher").Range("D5") = "1" And Sheets("Travel Expense Voucher").Range("Meals") <> "" And cmbOVNRTN = ("Overnight") And cmbInOutState = ("Out-of-State") Then
        Sheets("Expense Code Processing").Range("C2") = "62417"
End If
'''END MOVE OF MEALS EXPENSE CODES'''
 

Attachments

  • Final TravelForm 04272020 Continuation - for forum.xlsm
    119.6 KB · Views: 11
Last edited:
I think it would be helpful if you could walk us through a worked example, what we would input, what we would press, what resukts are produced now and what you want to happen in addition. To ask us to work all of that out in a fairly complex app without your background/context is asking a bit much.
 
I actually think I might have figured it out... but thank you. If I come up with more specific questions as I go, I will create a new post with my questions.
 
Back
Top