Results 1 to 10 of 39

Thread: Would someone please advise on a newbies code?

Hybrid View

Previous Post Previous Post   Next Post Next Post
  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?

    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,782
    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,782
    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

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
  •