Page 2 of 4 FirstFirst 1 2 3 4 LastLast
Results 11 to 20 of 39

Thread: Would someone please advise on a newbies code?

  1. #11
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,782
    Articles
    0
    Excel Version
    O365


    Register for a FREE account, and/
    or Log in to avoid these ads!

    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 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).

  2. #12
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,782
    Articles
    0
    Excel Version
    O365
    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?

  3. #13
    Acolyte KristenB's Avatar
    Join Date
    Jan 2020
    Posts
    35
    Articles
    0
    Excel Version
    365
    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.

  4. #14
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,782
    Articles
    0
    Excel Version
    O365
    Quote Originally Posted by KristenB View Post
    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.

    Quote Originally Posted by KristenB View Post
    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
    Code:
        With Worksheets("Travel Expense Voucher")
    Code:
            .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
    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
    Code:
        With Worksheets("Travel ")
           .Range("Q14:S14").Copy .Range("Q15:S15")
    I would use
    Code:
        With Worksheets("Travel Expense Voucher")
    Code:
            .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]"
    

    BTW, in your ExpenseCodeProcessing code you test the cell value for a value like so
    Code:
    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.

    Quote Originally Posted by KristenB View Post
    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.

    Quote Originally Posted by KristenB View Post
    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!

    Quote Originally Posted by KristenB View Post
    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 . 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.

    Quote Originally Posted by KristenB View Post
    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!

    Last edited by Bob Phillips; 2020-05-23 at 07:47 PM.

  5. #15
    Acolyte KristenB's Avatar
    Join Date
    Jan 2020
    Posts
    35
    Articles
    0
    Excel Version
    365
    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
    Code:
        With Worksheets("Travel Expense Voucher")
    Code:
            .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


    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
    Code:
        With Worksheets("Travel ")
           .Range("Q14:S14").Copy .Range("Q15:S15")
    I would use
    Code:
        With Worksheets("Travel Expense Voucher")
    Code:
            .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]"
    

    BTW, in your ExpenseCodeProcessing code you test the cell value for a value like so
    Code:
    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 . 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.

    Last edited by KristenB; 2020-05-23 at 08:47 PM.

  6. #16
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,782
    Articles
    0
    Excel Version
    O365
    Quote Originally Posted by KristenB View Post
    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.


    Quote Originally Posted by KristenB View Post
    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

    Code:
    .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.


    Quote Originally Posted by KristenB View Post
    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.

    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?


    Quote Originally Posted by KristenB View Post
    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.


    I should first mention that I changed the formula, you had
    =IF(OR(AJ14=FALSE,AJ14="0"),"0",IF(AND(AJ14=TRUE,K14="In-State"),"7.5","13"))


    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))


    The code to create this in VBA is
    Code:
    .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.

    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.


    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
    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.


    Quote Originally Posted by KristenB View Post
    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.

    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.


    Quote Originally Posted by KristenB View Post
    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.
    Quote Originally Posted by KristenB View Post

    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.


    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.


    Quote Originally Posted by KristenB View Post
    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.

    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.

  7. #17
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,782
    Articles
    0
    Excel Version
    O365
    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.

  8. #18
    Acolyte KristenB's Avatar
    Join Date
    Jan 2020
    Posts
    35
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by Bob Phillips View Post

    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


    Quote Originally Posted by Bob Phillips View Post

    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

    Code:
    .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?

    Quote Originally Posted by Bob Phillips View Post

    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.


    Quote Originally Posted by Bob Phillips View Post

    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.


    Quote Originally Posted by Bob Phillips View Post
    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:

    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.

  9. #19
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,782
    Articles
    0
    Excel Version
    O365
    Quote Originally Posted by KristenB View Post
    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.

    Quote Originally Posted by KristenB View Post

    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?

    Quote Originally Posted by KristenB View Post
    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:

    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.

    Quote Originally Posted by KristenB View Post
    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
    Last edited by Bob Phillips; 2020-05-24 at 08:02 PM.

  10. #20
    Acolyte KristenB's Avatar
    Join Date
    Jan 2020
    Posts
    35
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by Bob Phillips View Post
    [LEFT][COLOR=#333333][FONT=Verdana]

    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!!

Page 2 of 4 FirstFirst 1 2 3 4 LastLast

Tags for this Thread

Posting Permissions

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