Would someone please advise on a newbies code?

[KristenB]

I am fairly new to the world of VBA coding and feel that I am coming a long way, however, I am hoping that someone a bit more experienced than I, could look at my coding and tell me if I am on the right track. I know this is a bit long but I want to be sure that I am providing as much info as I can and am making things as clear as I can. Please bear with me.

I have a spreadsheet that data from a userform is moved to. This spreadsheet is primarily to 'house' what is presented by the user in a format that they are familiar with, and of course there are calculations, etc to provide further information. I have created a macro to 'unhide' an additional spreadsheet that I want to use to copy certain fields of each row of data in order to summarize in a useful format. This macro needs to determine if there is a value in the cell of a range of cells (I named the range on my worksheet as "mileage") and if so, copy that field and one other field in the row to the new spreadsheet, then I also need this macro to determine an additional code based on a couple of fields on the first spreadsheet and put that additional code on the new worksheet. Once this is done, it needs to go to the next row until all rows which contain the 'Mileage' range has been processed. Does all of that make sense? I will explain in practice and then I will post my code that I have thus far.

If row one has a value in the 'mileage' range cell, I need the 'mileage' cell and the 'projectcode' cell to copy to the new worksheet in the first empty row. Then, I need it to view the 'travelstate' range cell of the first worksheet and also a static field (D5), which together are used to determine which of 3 different codes to use for the additional field on my new worksheet using If and IfElse statements. Once this is completed, I need it to go to the second row and do the same steps, until all rows have been processed.

My code below shows first that, using my macro keys, I am 'unhiding' the worksheet where the data will transfer to. Next, it is determining if there is a value in the first cell of the 'Mileage' range and if there is, it copies that field and the 'projectcode' range cell of the same row to my new worksheet. These are copied to the first empty cell (I have done this by creating a 'cheater field' on a hidden spreadsheet which calculates how many fields are used to ensure my values move to the first empty. I think I did it correctly). Finally, my code is looking at the 'TravelState' and field D5 of the same row on the worksheet to determine the expense code and putting it in the field next to the mileage and projectcode values.

 Sub ExpenseCodeProcessing()'DataEntrySummary creation Keyboard Shortcut: Ctrl + Shift + Q'''


Worksheets("DataEntrySummary").Visible = True
Dim rngSource As Range
Dim col As Integer
Dim TargetRow As Integer
Dim CodesTargetRow As Integer


TargetRow = Sheets("Codes").Range("D43").Value + 1
CodesTargetRow = Sheets("Codes").Range("D45").Value + 1

'''DETERMINE EXPENSE CODE AND MOVE MILEAGE, PROJECT CODE AND EXPENSE CODE TO EXPENSE CODE PROCESSING WORKSHEET'''
For Each cell In Sheets("Travel Expense Voucher").Range("Mileage")
    If ActiveCell <> "" Then
    Sheets("Expense Code Processing").Range("Data_Start").Offset(CodesTargetRow, 0).Value = mileage
    Sheets("Expense Code Processing").Range("Data_Start").Offset(CodesTargetRow, 1).Value = projectcode
 
If Sheets("Travel Expense Voucher").Range("$D$5") = "2" Then
    Sheets("Expense Code Processing").Range("Data_Start").Offset(CodesTargetRow, 2).Value = "62494"
    ElseIf Sheets("Travel Expense Voucher").Range("$D$5") = "1" And TravelState = ("In-State") Then
        Sheets("Expense Code Processing").Range("Data_Start").Offset(CodesTargetRow, 2).Value = "62401"
    ElseIf Sheets("Travel Expense Voucher").Range("$D$5") = "1" And TravelState = ("Out-of-State") Then
        Sheets("Expense Code Processing").Range("Data_Start").Offset(CodesTargetRow, 2).Value = "62411"
End If
'''END MOVE OF MILEAGE VALUES WITH EXPENSE CODES'''

Just as a side, I want to note that I have two other 'sets' of similar steps that I need to have this macro accomplish and my goal is that it will complete all of the rows for this first set of steps prior to going to the next set of steps and do all of that 'set' for all rows prior to going to the next 'set' of similar steps. I understand that this macro might take a few seconds to run but I'm okay with that. The end results will be worth the wait.

Also, I know that I could probably provide a couple of additional 'dim's to shorten my coding and I appreciate the thoughts, but I prefer to keep it the way it is as it makes more sense to me being a novice, and in the event I have to go in at a later date and make tweaks, I want to be sure I'm not totally lost. I don't want to have to post again on here every time I have to make any changes or adjustments to my worksheet because I don't understand what the code says. I hope to someday be as capable as each of you but I'm just not there yet.

Thank you, in advance. I appreciate any help you can give me!

 

[Bob Phillips]

What exactly is the question?

 

[KristenB]

I was just hoping that someone could tell me if I have missed anything in my code to get it to do what I want? Does the coding look correct? Is it formatted properly?

 

[KristenB]

So I haven't heard from anyone so I was doing some messing around and found a few errors that were giving me issues. I have fixed those and have the following coding, however, my data is not going to my new spreadsheet? Can anyone help me?

Sub ExpenseCodeProcessing()'DataEntrySummary creation Keyboard Shortcut: Ctrl + Shift + Q'''


Worksheets("DataEntrySummary").Visible = True
Worksheets("ExpenseCodeProcessing").Visible = True
Dim col As Integer
Dim TargetRow As Integer
Dim CodesTargetRow As Integer


CodesTargetRow = Sheets("Codes").Range("$D$45").Value + 1




'''DETERMINE EXPENSE CODE AND MOVE MILEAGE, PROJECT CODE AND EXPENSE CODE TO EXPENSE CODE PROCESSING WORKSHEET'''
For Each Cell In Sheets("Travel Expense Voucher").Range("Mileage")
    If ActiveCell.Value <> "" Then
    Sheets("ExpenseCodeProcessing").Range("DataStartCodes").Offset(CodesTargetRow, 0).Value = mileage
    Sheets("ExpenseCodeProcessing").Range("DataStartCodes").Offset(CodesTargetRow, 1).Value = projectcode
If Sheets("Travel Expense Voucher").Range("$D$5") = "2" Then
    Sheets("ExpenseCodeProcessing").Range("DataStartCodes").Offset(CodesTargetRow, 2).Value = "62494"
    ElseIf Sheets("Travel Expense Voucher").Range("$D$5") = "1" And TravelState = ("In-State") Then
        Sheets("ExpenseCodeProcessing").Range("DataStartCodes").Offset(CodesTargetRow, 2).Value = "62401"
    ElseIf Sheets("Travel Expense Voucher").Range("$D$5") = "1" And TravelState = ("Out-of-State") Then
        Sheets("ExpenseCodeProcessing").Range("DataStartCodes").Offset(CodesTargetRow, 2).Value = "62411"
End If
    End If
Next


End Sub
'''END MOVE OF MILEAGE VALUES WITH EXPENSE CODES'''

 

[Bob Phillips]

Can you post the workbook, that would be more helpful.

 

[KristenB]

File attached

I'm happy to do that. It is attached. View attachment Final TravelForm 04272020 Continuation for forum.xlsm

 

[Bob Phillips]

I can see a number of problems with that code.

Firstly, you iterate through a range setting a variable (Cell) to each cell within that range.

[FONT=Verdana]For Each Cell In Sheets("Travel Expense Voucher").Range("Mileage")[/FONT]

So far so good, but you then don't access the cell via that variable, but use activecell

[FONT=Verdana]    If Activecell.Value <> "" Then[/FONT]

which means that you will be looking at the same cell all of the time, and that cell might not even be within your target range.

What you should use is

[FONT=Verdana]    For Each Cell In Sheets("Travel Expense Voucher").Range("Mileage")
    
        If Cell.Value <> vbNullString Then[/FONT]

vbNullString is a better test than "".

Secondly, you use three variable to load the target cells, mileage, projectcode, and TravleState, but you don't set these variables anywhere within your code, so you will be putting nothing into those cells.

You also test numbers as strings, 1 as "1", 62401 as "62401!, not necessary to have quotes around them.

Once you have set those variable, this is how I would write the code to make it a little more readable

[FONT=Verdana]    For Each Cell In Sheets("Travel Expense Voucher").Range("Mileage")
    
        If Cell.Value <> vbNullString Then
        
            With Worksheets("ExpenseCodeProcessing").Range("DataStartCodes")
            
                .Offset(CodesTargetRow, 0).Value = mileage
                .Offset(CodesTargetRow, 1).Value = projectcode
                If Worksheets("Travel Expense Voucher").Range("$D$5")[LEFT][COLOR=#222222][FONT=Verdana].Value[/FONT][/COLOR][/LEFT]
 = 2 Then
                
                    .Offset(CodesTargetRow, 2).Value = 62494
                    ElseIf Worksheets("Travel Expense Voucher").Range("$D$5")[LEFT][COLOR=#222222][FONT=Verdana].Value [/FONT][/COLOR][/LEFT]
= 1 And TravelState = ("In-State") Then
                    
                        .Offset(CodesTargetRow, 2).Value = 62401
                    ElseIf Worksheets("Travel Expense Voucher").Range("$D$5")[LEFT][COLOR=#222222][FONT=Verdana].Value[/FONT][/COLOR][/LEFT]
 = 1 And TravelState = ("Out-of-State") Then
                   
                        .Offset(CodesTargetRow, 2).Value = 62411
                End If
            End With
        End If
    Next[/FONT]

 

[KristenB]

Code isn't working?

Absolute genius! I have updated all of my code according to the coding you are showing here, and I am not getting any errors, and when I attempt to run the code it seems to be doing something but it isn't copying the data to the worksheet. I have uploaded the updated workbook, would you please look at it for me and tell me what I am missing? I'm sure I goofed it up somehow?

 

[Bob Phillips]

In your submit button code, you have statements to move the form data to the Travel Expense Voucher, and even though you end it with the comment [FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]'''END DATA MOVE INTO DATABASE''', you are not copying it to the worksheet ExpenseCodeProcessing.

But why have a form, copy it to a printable worksheet, and then to the database? Why not just enter the details straight into the Travel Expense Voucher sheet?
[/FONT]

 

[KristenB]

Thank you for looking. I wouldn't object, necessarily to entering into the Travel Expense Voucher sheet except that it makes the form seem so intimidating to the users who are not computer friendly. This is what the printable form currently looks like and I'm trying to make less stress for the user by keeping it as 'similar' as possible. Also, I need it to be in vertical columns, the the new sheet, in order to run a pivot table for ease of data entry. Currently, we have the form just as you see but then we have to manually 'summarize' the data for entry as we can only enter one of each project code, expense code combination into our system.

Therefore, the goal is for them to use the userform to enter their travel, which can at times be horrendous amounts, and I feel that the userform will hopefully be a bit less intimidating and will also direct the user to enter the information that is needed and not leave fields blank, decreasing the amount of follow-up that processors have to perform. So once the form is received in the processing office, the processor activates the macro (Ctrl+Alt+Q) which opens up the expense code processing sheet and the data entry summary sheet, moves the data and determined expense codes to the expense code processing sheet in vertical columns. Then the processor 'refreshes all' in the data screen and the summary is ready for entry.

Since this is a separate macro, I didn't think that I would need a 'submit' command. If I need to create a button or something to do this, I'm okay with that. I would create it on the Data Entry Summary worksheet so it could all be done on the same screen for the processor. Is this what is needed to make it all work? Can the macro not perform all steps in one macro? Please remember, I am a newbie at this and am still learning so please be patient with me.

 

[Bob Phillips]

I understand the need for the table like set of data for pivotting, but that does not need the userform to get there. You could have just the same validation on the Travel worksheet as you have with the userform, and it is a lot easier to manage. Personally, I try to avoid userforms wherever I can, I think they are the antithesis of user-friendly, a worksheet is so much nicer, and much quicker to input, but it's your app so you need to do what you feel is correct.

If I understand the workflow process, a user would input their travel claims, and their accommodation/meal claims on a daily basis? This would either be on the userform and captured on Travel sheet, or direct to the Travel sheet if I were building it.

Then on a periodic basis, would that be weekly or monthly, they get the claim approved, and it is at that point that the data on the Travel sheet is copied to the database sheet.

If that is so, I think you need to add a call to the [FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]ExpenseCodeProcessing procedure in the submit button code on the Signature form. How does the Travel worksheet gret cleared should that happen when you copy it to the database sheet?

But how will you consolidate all of the users data, your database and Bill's database, and Melanie's database, they will all be in separate workbooks (Power Query would be good for this).
[/FONT]

 

[Bob Phillips]

Just noticed that you said you need the data in pivotable for for data entry. I guess this means that you are loading this data into another system, couldn't you do that directly from this Excel workbook, save the data entry task?

 

[KristenB]

OMG... I wish we could do it directly from this form but the system we are entering into is quite tedious and painful as it is. We struggle to get our developers to do anything special let alone this too.

Yes, each day the user will complete their travel and save it to their computer. I haven't set a way for the form to clear, I would love to as long as I don't lose my hidden formulas on the Travel Expense Voucher sheet. But my instructions will then be that each person needs to use a 'new' sheet each month/week. These are usually submitted on a monthly basis but no more than one month at a time and no less than two weeks. I would prefer to require a month at a time only but the higher powers are in control of that.

The user will simply put this into an email and send it to their supervisor who will approve it and then send it on to the processing department. Then, when it gets to the processing department, they will initiate the macro and do the entry. We currently save all files on a network drive that is locked down to only specific people to view, saved by the employees name and the dates reported. This allows us to go in an ensure that we aren't paying for a travel reimbursement request more than one time. It is a very manual process.

The software application that this information is entered into will track per employee but we must also maintain these records separately to ensure we have them for auditing purposes.

I agree with you on having the user enter into the worksheet but as they currently do so, they leave out information that is required too often and then we spend hours following up to ensure that we have the information. We would like to send it back and just tell the employee to try again but i'm hoping that once I have this in place, it will remove that issue as the userform cannot be submitted without all of the appropriate information. Besides that, my supervisor wants it to be in a userform format to make it easier. So I am also trying to please those requirements. I thought about just having a worksheet that they enter the data onto and submit and it goes to the form, but they will want to see what they have as it goes so they know what it looks like. Staff will panic if they don't see it in the same format that they have been using. I am dealing with a group of older persons who are not computer friendly and they hate changes. Every time we make a change they tell us how much they hate change, but this is something that is necessary for the sake of entry as well as ensuring we are getting the information we need to process.

Instead of a command on an approval, would it work to just insert a button to call the commands? The macro could still be used to unhide the sheets that I don't want outside staff touching, and then the button could be on one of those worksheets? What do you think? I realize this will be an additional step for processing and is manual, but it would be so much better still than what we have now.

 

[Bob Phillips]

OMG... I wish we could do it directly from this form but the system we are entering into is quite tedious and painful as it is. We struggle to get our developers to do anything special let alone this too.

A familiar story, developers like to take their time, and they think Excel; is not 'real computing' ... idiots. But I was suggesting that you write it directly to the other system, no developers involved. Of course, you would need IT's blessing, but using ADO you can write from Excel to SQL Server, Oracle, or many other systems.

Yes, each day the user will complete their travel and save it to their computer. I haven't set a way for the form to clear, I would love to as long as I don't lose my hidden formulas on the Travel Expense Voucher sheet.

You would only clear the expenses area. something like

    With Worksheets("Travel Expense Voucher")

[FONT=Verdana]        .Range("B14:U20").ClearContents
        .Range("B24:K27").ClearContents
    
        .Range("EEDate").MergeArea.ClearContents
        .Range("EEName").MergeArea.ClearContents
        .Range("EECompName").MergeArea.ClearContents
        .Range("EECompUName").MergeArea.ClearContents
    
        .Range("ORDate").MergeArea.ClearContents
        .Range("ORName").MergeArea.ClearContents
        .Range("ORCompName").MergeArea.ClearContents
        .Range("ORCompUName").MergeArea.ClearContents
    
        .Range("DODate").MergeArea.ClearContents
        .Range("DOName").MergeArea.ClearContents
        .Range("DOCompName").MergeArea.ClearContents
        .Range("DOCompUName").MergeArea.ClearContents
 End With[/FONT]

It would be better to give the travel and other expenses range a name and use that, the code can flex then.
BTW, I notice that you put formulae in columns Q:S from row 14, but what happens when you need to populate row 14? I would load them via VBA, and make you formulae better at the same time (you keep enclosing numbers in quotes, very bad practice, and it stops your formats from working correctly). And when you sum, don't use
=SUM(Q14+R14+S14),
either use
=Q14+R14+S14
or
=SUM(Q14,R14,S14)
So, instead of

    With Worksheets("Travel ")
       .Range("Q14:S14").Copy .Range("Q15:S15")

I would use

    With Worksheets("Travel Expense Voucher")

[FONT=Verdana]        .Cells(TargetRow, 15).FormulaR1C1 = "=IF(OR(NOT(RC[19]),RC[19]=0),0,IF(AND(RC[19],RC[-6]=""In-State""),7.5,13))"
        .Cells(TargetRow, 16).FormulaR1C1 = "=IF(OR(NOT(RC[19]),RC[19]=0),0,IF(AND(RC[19],RC[-6]=""In-State""),8.5,14))"
        .Cells(TargetRow, 17).FormulaR1C1 = "=IF(OR(NOT(RC[19]),RC[19]=0),0,IF(AND(RC[19],RC[-8]=""In-State""),14.5,23))"
        .Cells(TargetRow, 18).FormulaR1C1 = "=RC[-3]+RC[-2]+RC[-1]"
[/FONT]

BTW, in your ExpenseCodeProcessing code you test the cell value for a value like so

cell.Value = "$12.00"

This is not good, the Value will be a number, if you load it correctly. The .Text property can be tested such, but the .Value should be tested for just the number, = 12.

The user will simply put this into an email and send it to their supervisor who will approve it and then send it on to the processing department. Then, when it gets to the processing department, they will initiate the macro and do the entry. We currently save all files on a network drive that is locked down to only specific people to view, saved by the employees name and the dates reported. This allows us to go in an ensure that we aren't paying for a travel reimbursement request more than one time. It is a very manual process.

You are not kidding, it can be so much more automated. I would personally do it so that the emailing is a macro, then when the super signs off, write to the database, write to the ERP system, save in the network folder. TRhen it is just two button presses, code does all the rest. AT the very least automate the emailing and saving to the database.

The software application that this information is entered into will track per employee but we must also maintain these records separately to ensure we have them for auditing purposes.

Your Excel workbooks are your audit trail? Wow!

I agree with you on having the user enter into the worksheet but as they currently do so, they leave out information that is required too often and then we spend hours following up to ensure that we have the information. We would like to send it back and just tell the employee to try again but i'm hoping that once I have this in place, it will remove that issue as the userform cannot be submitted without all of the appropriate information. Besides that, my supervisor wants it to be in a userform format to make it easier. So I am also trying to please those requirements. I thought about just having a worksheet that they enter the data onto and submit and it goes to the form, but they will want to see what they have as it goes so they know what it looks like. Staff will panic if they don't see it in the same format that they have been using. I am dealing with a group of older persons who are not computer friendly and they hate changes. Every time we make a change they tell us how much they hate change, but this is something that is necessary for the sake of entry as well as ensuring we are getting the information we need to process.

Lady, I am an older person, we can deal with it believe me :amen:. As I mentioned earlier, you can apply all of the userform validation to the worksheet. If you automate the emailing, you can stop them doing so until it is all completed, all valid, all integral.

Instead of a command on an approval, would it work to just insert a button to call the commands? The macro could still be used to unhide the sheets that I don't want outside staff touching, and then the button could be on one of those worksheets? What do you think? I realize this will be an additional step for processing and is manual, but it would be so much better still than what we have now.

Of course you could, but why would you? You already have that button, Sign/Approve. I would do so that the employee fills in their details and hits that button., That emails it to their supervisor. The supervisor fills in their details and hits that button, that emails it to the director. They fill in their details, and that saves it to the worksheet database (and preferably updates the ERP).
Automate, automate, automate Kristen!

 

[KristenB]

A familiar story, developers like to take their time, and they think Excel; is not 'real computing' ... idiots. But I was suggesting that you write it directly to the other system, no developers involved. Of course, you would need IT's blessing, but using ADO you can write from Excel to SQL Server, Oracle, or many other systems.

That is the problem, getting IT's blessing. That will not happen I can guarantee.

You would only clear the expenses area. something like

    With Worksheets("Travel Expense Voucher")

[FONT=Verdana]        .Range("B14:U20").ClearContents
        .Range("B24:K27").ClearContents
    
        .Range("EEDate").MergeArea.ClearContents
        .Range("EEName").MergeArea.ClearContents
        .Range("EECompName").MergeArea.ClearContents
        .Range("EECompUName").MergeArea.ClearContents
    
        .Range("ORDate").MergeArea.ClearContents
        .Range("ORName").MergeArea.ClearContents
        .Range("ORCompName").MergeArea.ClearContents
        .Range("ORCompUName").MergeArea.ClearContents
    
        .Range("DODate").MergeArea.ClearContents
        .Range("DOName").MergeArea.ClearContents
        .Range("DOCompName").MergeArea.ClearContents
        .Range("DOCompUName").MergeArea.ClearContents
 End With[/FONT]

Ooo... I like this! Thank you for the suggestion. I had no idea I could even do that!

It would be better to give the travel and other expenses range a name and use that, the code can flex then.

Can you tell me where you are referencing with this statement? I tried to use ranges everywhere that I could. I would love to correct that!

BTW, I notice that you put formulae in columns Q:S from row 14, but what happens when you need to populate row 14? I would load them via VBA, and make you formulae better at the same time (you keep enclosing numbers in quotes, very bad practice, and it stops your formats from working correctly). And when you sum, don't use
=SUM(Q14+R14+S14),
either use
=Q14+R14+S14
or
=SUM(Q14,R14,S14)
So, instead of

    With Worksheets("Travel ")
       .Range("Q14:S14").Copy .Range("Q15:S15")

I would use

    With Worksheets("Travel Expense Voucher")

[FONT=Verdana]        .Cells(TargetRow, 15).FormulaR1C1 = "=IF(OR(NOT(RC[19]),RC[19]=0),0,IF(AND(RC[19],RC[-6]=""In-State""),7.5,13))"
        .Cells(TargetRow, 16).FormulaR1C1 = "=IF(OR(NOT(RC[19]),RC[19]=0),0,IF(AND(RC[19],RC[-6]=""In-State""),8.5,14))"
        .Cells(TargetRow, 17).FormulaR1C1 = "=IF(OR(NOT(RC[19]),RC[19]=0),0,IF(AND(RC[19],RC[-8]=""In-State""),14.5,23))"
        .Cells(TargetRow, 18).FormulaR1C1 = "=RC[-3]+RC[-2]+RC[-1]"
[/FONT]

BTW, in your ExpenseCodeProcessing code you test the cell value for a value like so

cell.Value = "$12.00"

This is not good, the Value will be a number, if you load it correctly. The .Text property can be tested such, but the .Value should be tested for just the number, = 12.

I had actually wanted to do that but wasn't able to figure out how. I mentioned it on a forum and that was the only response suggestion that I received. It is difficult because there are so many time ranges involved. If travel is performed between certain hours in the morning and for at least a certain amount of time, then they are eligible for the morning meal, otherwise, they aren't. The lunch meal is even more difficult because there are specific start and end ranges and they have to be in travel status for more than 3 hours to be eligible for that meal. Then it depends on several other factors of what amounts they are eligible to receive. I couldn't get anyone that could help me with anything more than that so I just made it happen that way. LOL

The formulae in Q:S from row 14 includes a copy of the previous row if more than 2 rows are used. Does that make sense? So then it begins to add rows including all of the formulae in those fields. If you can help me figure that out, I would consider you my hero! LOL

I will, however, look at my other coding as you mentioned above and see if I can find where you are seeing this coding issue, and will make the corrections needed. Can you tell me what is meant by the RC in your code above? If I could understand it that would help but I'm not sure what you are doing with that code?

You are not kidding, it can be so much more automated. I would personally do it so that the emailing is a macro, then when the super signs off, write to the database, write to the ERP system, save in the network folder. TRhen it is just two button presses, code does all the rest. AT the very least automate the emailing and saving to the database.

Yes, I know. I have tried to get my supervisor to allow for more automation but there are too many people involved and many of them are too set in their ways of what we can do. Besides that, I don't know enough to make things too automated since I am literally learning as I go. LOL And of course there is no budget to purchase anything in the way of a new software application, so this is what I have.

Your Excel workbooks are your audit trail? Wow!

I know!! Right? But it is better than the paper copies of these that people send in that we can't ready half of them, at least this way it is in a standard text format. Not to mention, why do we have to have so much darn paper! I hate killing trees when there are so many other options.

Lady, I am an older person, we can deal with it believe me :amen:. As I mentioned earlier, you can apply all of the userform validation to the worksheet. If you automate the emailing, you can stop them doing so until it is all completed, all valid, all integral.

Hmmmm that is certainly something to consider. The only problem is that it doesn't always have to go to the Director's office, only under certain situations. And each of the different areas of our company have different Director personnel that it would go to so it isn't always a specific person.

Of course you could, but why would you? You already have that button, Sign/Approve. I would do so that the employee fills in their details and hits that button., That emails it to their supervisor. The supervisor fills in their details and hits that button, that emails it to the director. They fill in their details, and that saves it to the worksheet database (and preferably updates the ERP).
Automate, automate, automate Kristen!

Another great idea but each person has a different supervisor to send these to. So I couldn't actually set the system to automatically email it to a specific person. Does that make sense? I might try using a button and see if that will work for me. If I were to do that, should I have a different module to put that coding into? Or would it stay in the same module I have it in? Maybe another module would be better?

I truly appreciate all of your help. I have been working on this project for several months and I have great ideas but I have struggled to find the help I need on some of the pieces. I am at my office right now but will be home soon and intend to look at this then. The file, unfortunately, wouldn't email to my office email so I can't see it right now. I tried to use the 'quote' function but obviously I'm not quite sure what to do. I apologize for the garble.

 

[Bob Phillips]

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]

That is the problem, getting IT's blessing. That will not happen I can guarantee.

I don't want to labour the point, but you should try, it is the game changer for you. An alternative you could offer them if they say no is that they give you access to a staging table where you write the details to and they write some code to update the ERP from there.

[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]

Can you tell me where you are referencing with this statement? I tried to use ranges everywhere that I could. I would love to correct that!

I am referring to the ranges "B14:U20" and "B24:K27". If you select each range and name them, such as TravelExpenses and OtherExpenses, the code would then be[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]

.Range("TravelExpenses").ClearContents
        .Range("OtherExpenses").ClearContents

More flexible coding, and you can extra rows in the middle f those ranges, not after, and it will all still work.

[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]

The formulae in Q:S from row 14 includes a copy of the previous row if more than 2 rows are used. Does that make sense? So then it begins to add rows including all of the formulae in those fields. If you can help me figure that out, I would consider you my hero!

I think I understand that, but as I said how does the formula get into row 14? I probably know now, because you are manually clearing the sheet you probably don't clear Q14:S14, but others might, and if you adopt my code for clearing the Travel Expense Voucher, that certainly will.[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]But haven't I already given you the code for dealing with that by writing the formulae in VBA when you fill in a row of the claim form?

[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]

I will, however, look at my other coding as you mentioned above and see if I can find where you are seeing this coding issue, and will make the corrections needed. Can you tell me what is meant by the RC in your code above? If I could understand it that would help but I'm not sure what you are doing with that code?

I'll try and explain it for you. I'll take the formula in Q and walk you through it.

[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]I should first mention that I changed the formula, you had[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=IF(OR(AJ14=FALSE,AJ14="0"),"0",IF(AND(AJ14=TRUE,K14="In-State"),"7.5","13"))

[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]and I changed it to simplify and make more efficient, to
=IF(OR(NOT(AJ14),AJ14=0),0,IF(AND(AJ14,K14="In-State"),7.5,13))

[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]The code to create this in VBA is

.Cells(TargetRow, 15).FormulaR1C1 = "=IF(OR(NOT(RC[19]),RC[19]=0),0,IF(AND(RC[19],RC[-6]=""In-State""),7.5,13))"

the 15 after Targetrow is referring to column Q, which is column 17, but you are starting at Data_Start which is column B.[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]FormulaR1C1 means that you are using row and column number notation, rather than row number column letter as you normally see in Excel. You can actually see it in R1C1 notation on the worksheet, just go to File>Options>Formulas>Working with formulas and check the R1C1 reference style checkbox. The disadvantage of this style is that you are writing formulas differently to how you normally see them in Excel, the advantage is that you can use number variable for both the row and column. I would venture that most serious developers always use R1C1 style in VBA.

[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]R refers to the row being addressed, C refers to the column if you have R or C with no qualification, it refers to the current row/column. If you have a number after it, that is the same as locking the row or column in Excel formula, so R2C2 is the same as $B$2. Putting a number in square brackets means that it is offset from the current row/column. So the code

range("O23").formular1c1 ="=R[3]C[-2]=""ABC"""

sets cell 023 to =M26="ABC", 3 rows after the current row, 2 columns before the current column. You need to double quotes for text strings.

[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]

I have tried to get my supervisor to allow for more automation but there are too many people involved and many of them are too set in their ways of what we can do. Besides that, I don't know enough to make things too automated since I am literally learning as I go. LOL And of course there is no budget to purchase anything in the way of a new software application, so this is what I have.

[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Sell it to them. Tell them you are doing it to make sure that they get the correct expenses, and the new approach means they should get it faster. You don't need new software, it can all be done in Excel.

[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]

Hmmmm that is certainly something to consider. The only problem is that it doesn't always have to go to the Director's office, only under certain situations. And each of the different areas of our company have different Director personnel that it would go to so it isn't always a specific person.[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]
Another great idea but each person has a different supervisor to send these to. So I couldn't actually set the system to automatically email it to a specific person. Does that make sense? I might try using a button and see if that will work for me. If I were to do that, should I have a different module to put that coding into? Or would it stay in the same module I have it in? Maybe another module would be better?

I thought that would be the case, and the way I would do it would be to have a table of employees, email address, name of supervisor and email, name of director and email. There must be a rule that determines who and when, otherwise the employees wouldn't know, so you build those rules into the code. It would be better to have that table on a central server database and query it when the expenses workbook gets opened, that way you can keep a single copy up-to-date. But I think I know what you will say.

[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]As for different modules, it is not necessary, but I like to have lots of modules, each with functionally specific code, so I would have a mail module with all of the mailing procedures and call them from my main procedure.

[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]

I truly appreciate all of your help. I have been working on this project for several months and I have great ideas but I have struggled to find the help I need on some of the pieces. I am at my office right now but will be home soon and intend to look at this then. The file, unfortunately, wouldn't email to my office email so I can't see it right now. I tried to use the 'quote' function but obviously I'm not quite sure what to do. I apologize for the garble.

[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]I saw this last night but it was late, I am in the UK, so I didn't reply then so that I could take my time and hopefully be somewhat clearer with my explanations.
[/FONT]

 

[Bob Phillips]

BTW, those columns for setting the formulas in VBA should be 16, 17 and 18, not 15, 16 and 17, I was thinking Offset, but not using Offset.

 

[KristenB]

I don't want to labour the point, but you should try, it is the game changer for you.

Certainly something I can try. But first I will have to prove to them that I can do this. LOL

I am referring to the ranges "B14:U20" and "B24:K27". If you select each range and name them, such as TravelExpenses and OtherExpenses, the code would then be

.Range("TravelExpenses").ClearContents
        .Range("OtherExpenses").ClearContents

More flexible coding, and you can extra rows in the middle f those ranges, not after, and it will all still work.

Gotcha! I think I can do this! This part would be easy now that I know how! If I name the ranges, I could use the names throughout the rest of my code too, right?

I think I understand that, but as I said how does the formula get into row 14?

Oh! I see what you are asking. This was the hardest part of my coding by far I think. I sought out help and was given only the one option that I could incorporate. If you unhide the columns to the right of the form between U and AV, you will see how I had to this. It is quite difficult and if I could figure out how to lock that piece down only, I would do it in a heartbeat. LOL That piece took me days (around my regular job that is) to get incorporated.

I'll try and explain it for you. I'll take the formula in Q and walk you through it.

Oooo... I figured R was for row and C for column but I couldn't get the pieces where it was just RC, but that makes sense!

The hidden fields between columns U and AV are the reasons that I don't know if this would work? I would love to do this and remove the hidden columns, but I just don't know how to make that happen? I know I spent a couple of weeks researching and trying different things and then I reached out to a forum and that was the only option I was given. It was confusing to me but I finally figured it out and got it to work right. If you could offer an option for that as well! I would take it!

The problem is, there are so many factors that it is dependent on and different time ranges. Lunch is by far the most difficult part because what has to be done is if they are in travel status for at least 3 hours and 1 minute, between the hours of 10:01 am and 3:00 pm, then they get lunch. It was very difficult to do that coding since entry is only the leave time and return times. So there wasn't anything for code to look at for that specific meal. Oh, it also has to tie to their normal working hours for each meal too. And then it depends on if they are in-state or out-of-state. So many pieces.

Sell it to them. Tell them you are doing it to make sure that they get the correct expenses, and the new approach means they should get it faster. You don't need new software, it can all be done in Excel.

I'm going to try. But in the meantime I need to get this into a functional format for use. I am so sad for the staff that has to manually summarize these all the time. I need to come up with a solution/help for them and their poor brains. LOL Not to mention, save some time!

So what I did for now, is I created a GO button on the summary sheet to pull the data into the expense processing worksheet but nothing is pulling over. I even created a message to show at the end of the code and that works fine but my values aren't coming into the worksheet? I can't seem to figure out what I am missing. I know it is something small and silly but I can't seem to find it. Here is my code:

Public Sub cmdGo_Click()'''When GO button is clicked, process data to expense code processing worksheet'''


Dim CodesTargetRow As Integer


CodesTargetRow = Sheets("Codes").Range("$D$45").Value + 1


'''DETERMINE EXPENSE CODE AND MOVE MILEAGE, PROJECT CODE AND EXPENSE CODE TO EXPENSE CODE PROCESSING WORKSHEET'''
For Each cell In Sheets("Travel Expense Voucher").Range("Mileage")
    If cell.Value <> vbNullString Then
        With Worksheets("ExpenseCodeProcessing").Range("DataStartCodes")
            .Offset(CodesTargetRow, 0).Value = mileage
            .Offset(CodesTargetRow, 1).Value = projectcode
            If Worksheets("Travel Expense Voucher").Range("$D$5").Value = 2 Then
            .Offset(CodesTargetRow, 2).Value = 62494
            ElseIf Worksheets("Travel Expense Voucher").Range("$D$5").Value = 1 And TravelState = ("In-State") Then
            .Offset(CodesTargetRow, 2).Value = 62401
            ElseIf Worksheets("Travel Expense Voucher").Range("$D$5").Value = 1 And TravelState = ("Out-of-State") Then
            .Offset(CodesTargetRow, 2).Value = 62411
            End If
        End With
    End If
Next
'''END MOVE OF MILEAGE VALUES WITH EXPENSE CODES'''


'''MOVE MEAL VALUES WITH EXPENSE CODE TO EXPENSE CODE PROCESSING DATABASE'''
For Each cell In Sheets("Travel Expense Voucher").Range("Meals")
    If cell.Value <> vbNullString Then
        With Worksheets("ExpenseCodeProcessing").Range("DataStartCodes")
            .Offset(CodesTargetRow, 0).Value = meals
            .Offset(CodesTargetRow, 1).Value = projectcode
            If Worksheets("Travel Expense Voucher").Range("$D$5").Value = 2 Then
            .Offset(CodesTargetRow, 2).Value = 62495
            ElseIf Worksheets("Travel Expense Voucher").Range("$D$5").Value = 1 And TravelState = ("In-State") And Overnight_or_Return = ("Return") Then
            .Offset(CodesTargetRow, 2).Value = 62407
            ElseIf Worksheets("Travel Expense Voucher").Range("$D$5").Value = 1 And TravelState = ("In-State") And Overnight_or_Return = ("Overnight") Then
            .Offset(CodesTargetRow, 2).Value = 62410
            ElseIf Worksheets("Travel Expense Voucher").Range("$D$5").Value = 1 And TravelState = ("Out-of-State") And Overnight_or_Return = ("Return") Then
            .Offset(CodesTargetRow, 2).Value = 62417
            ElseIf Worksheets("Travel Expense Voucher").Range("$D$5").Value = 1 And TravelState = ("Out-of-State") And Overnight_or_Return = ("Overnight") Then
            .Offset(CodesTargetRow, 2).Value = 62430
            End If
        End With
    End If
Next
'''END MOVE OF MEALS EXPENSE CODES'''


'''MOVE LODGING VALUES WITH EXPENSE CODE TO EXPENSE CODE PROCESSING DATABASE'''
For Each cell In Sheets("Travel Expense Voucher").Range("Lodging")
    If cell.Value <> vbNullString Then
        With Worksheets("ExpenseCodeProcessing").Range("DataStartCodes")
            .Offset(CodesTargetRow, 0).Value = lodging
            .Offset(CodesTargetRow, 1).Value = projectcode
            If Worksheets("Travel Expense Voucher").Range("$D$5").Value = 2 And cell.Value = 12 Then
            .Offset(CodesTargetRow, 2).Value = 62497
            ElseIf Worksheets("Travel Expense Voucher").Range("$D$5").Value = 2 And cell.Value <> 12 Then
            .Offset(CodesTargetRow, 2).Value = 62498
            ElseIf Worksheets("Travel Expense Voucher").Range("$D$5").Value = 1 And cell.Value = 12 And TravelState = ("In-State") Then
            .Offset(CodesTargetRow, 2).Value = 62406
            ElseIf Worksheets("Travel Expense Voucher").Range("$D$5").Value = 1 And cell.Value <> 12 And TravelState = ("In-State") Then
            .Offset(CodesTargetRow, 2).Value = 62408
            ElseIf Worksheets("Travel Expense Voucher").Range("$D$5").Value = 1 And cell.Value = 12 And TravelState = ("Out-of-State") Then
            .Offset(CodesTargetRow, 2).Value = 62416
            ElseIf Worksheets("Travel Expense Voucher").Range("$D$5").Value = 1 And cell.Value <> 12 And TravelState = ("Out-of-State") Then
            .Offset(CodesTargetRow, 2).Value = 62418
            End If
        End With
    End If
Next
'''END MOVE OF LODGING VALUES WITH EXPENSE CODES'''


'''MOVE OTHER EXPENSE VALUES WITH EXPENSE CODE TO EXPENSE CODE PROCESSING DATABASE'''
For Each cell In Sheets("Travel Expense Voucher").Range("OtherAmount")
    If cell.Value <> vbNullString Then
        With Worksheets("ExpenseCodeProcessing").Range("DataStartCodes")
            .Offset(CodesTargetRow, 0).Value = otheramount
            .Offset(CodesTargetRow, 1).Value = otherprojectcode
            .Offset(CodesTargetRow, 2).Value = otherexpensecode
        End With
    End If
Next
'''END MOVE OF OTHER EXPENSE VALUES WITH EXPENSE CODES'''


MsgBox "Data has been processed.  Select 'Data/Refresh All' to create summary"

End Sub

Absolutely no problem on timing! I just appreciate that you are helping me so much with this and that you responded at all. I was afraid that I wasn't going to get a response.

I am definitely going to work on the 'clear form' coding so they don't have to get a new form each month. That will be a huge help in itself.

 

[Bob Phillips]

​

If I name the ranges, I could use the names throughout the rest of my code too, right?

Most certainly yes, IMO you should always use named ranges and never refer to cells by the worksheet reference.

​

The hidden fields between columns U and AV are the reasons that I don't know if this would work? I would love to do this and remove the hidden columns, but I just don't know how to make that happen? I know I spent a couple of weeks researching and trying different things and then I reached out to a forum and that was the only option I was given. It was confusing to me but I finally figured it out and got it to work right. If you could offer an option for that as well! I would take it!

The problem is, there are so many factors that it is dependent on and different time ranges. Lunch is by far the most difficult part because what has to be done is if they are in travel status for at least 3 hours and 1 minute, between the hours of 10:01 am and 3:00 pm, then they get lunch. It was very difficult to do that coding since entry is only the leave time and return times. So there wasn't anything for code to look at for that specific meal. Oh, it also has to tie to their normal working hours for each meal too. And then it depends on if they are in-state or out-of-state. So many pieces.

I can take a look, there has to be a set of rules that we can build an algorithm from. Where in your code do those columns get populated?​

So what I did for now, is I created a GO button on the summary sheet to pull the data into the expense processing worksheet but nothing is pulling over. I even created a message to show at the end of the code and that works fine but my values aren't coming into the worksheet? I can't seem to figure out what I am missing. I know it is something small and silly but I can't seem to find it. Here is my code:

Public Sub cmdGo_Click()'''When GO button is clicked, process data to expense code processing worksheet'''
Dim CodesTargetRow As Integer
CodesTargetRow = Sheets("Codes").Range("$D$45").Value + 1

'''DETERMINE EXPENSE CODE AND MOVE MILEAGE, PROJECT CODE AND EXPENSE CODE TO EXPENSE CODE PROCESSING WORKSHEET'''
For Each cell In Sheets("Travel Expense Voucher").Range("Mileage")
… etc.

I see so many problems here, lots of variables being used that are not being setup, and it is slooooooooow. But it may be that my version is too out of date, can you post an up-to-date version with the Go button and code for me to see.

Absolutely no problem on timing! I just appreciate that you are helping me so much with this and that you responded at all. I was afraid that I wasn't going to get a response.

I cannot believe someone wouldn't have jumped in to help, often we are tripping over each other. Most time an OP doesn't get a response is because the problem is horrendously complex (usually user-made complexity), or it has been so badly explained (or not explained) that no-one understands it

 

[KristenB]

I can take a look, there has to be a set of rules that we can build an algorithm from. Where in your code do those columns get populated?​

I see so many problems here, lots of variables being used that are not being setup, and it is slooooooooow. But it may be that my version is too out of date, can you post an up-to-date version with the Go button and code for me to see.


I cannot believe someone wouldn't have jumped in to help, often we are tripping over each other. Most time an OP doesn't get a response is because the problem is horrendously complex (usually user-made complexity), or it has been so badly explained (or not explained) that no-one understands it​

Thank you, Bob! I have attached my updated file. I was working on trying to figure out how to lock down specific fields with formulas when you responded to me. I have figured out how to protect the sheets okay but not the cells that I need to lock on the Travel Expense Form worksheet. I am still working on that.

The fields in the hidden columns come from the userform. You will also see that the checkboxes in the userform are populated based on the meals that the user is allowed based on the times provided and the algorithm in the hidden columns.

To get the GO button to show, use the Macro CTRL+ALT+Q. That will open up the DataEntrySummary worksheet which is where the button is. I'm sure there are a lot of problems and I would love to figure out how to fix them, whatever suggestions you might have, I'm thrilled to receive. You will also see that I put a button at the bottom of the form for the user to clear the form. Thank you for that suggestion! It will be so wonderful to have that option for staff!

Thank you so much for your help!!​