Page 1 of 4 1 2 3 ... LastLast
Results 1 to 10 of 39

Thread: Would someone please advise on a newbies code?

  1. #1
    Acolyte KristenB's Avatar
    Join Date
    Jan 2020
    Posts
    35
    Articles
    0
    Excel Version
    365

    Would someone please advise on a newbies code?



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

    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.

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

  2. #2
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,781
    Articles
    0
    Excel Version
    O365
    What exactly is the question?

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

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

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

  5. #5
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,781
    Articles
    0
    Excel Version
    O365
    Can you post the workbook, that would be more helpful.

  6. #6
    Acolyte KristenB's Avatar
    Join Date
    Jan 2020
    Posts
    35
    Articles
    0
    Excel Version
    365

    File attached

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

  7. #7
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,781
    Articles
    0
    Excel Version
    O365
    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.
    Code:
    For Each Cell In Sheets("Travel Expense Voucher").Range("Mileage")
    So far so good, but you then don't access the cell via that variable, but use activecell
    Code:
        If Activecell.Value <> "" Then
    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
    Code:
        For Each Cell In Sheets("Travel Expense Voucher").Range("Mileage")
        
            If Cell.Value <> vbNullString Then

    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
    Code:
        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
    Last edited by Bob Phillips; 2020-05-23 at 12:15 PM.

  8. #8
    Acolyte KristenB's Avatar
    Join Date
    Jan 2020
    Posts
    35
    Articles
    0
    Excel Version
    365

    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?
    Attached Files Attached Files

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

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

Page 1 of 4 1 2 3 ... 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
  •