Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    New Lounger
    Join Date
    Mar 2007
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel VBA Challenge (Excel 2003/)

    Greetings:

    I have pretty solid Excel skills but a novice Excel VBA user and having facing some challenges with the attached routine which I wrote. There are two sheets contained in a workbook: Raw Data and Report. The information on the Raw Data worksheet needs to be copied to the Report worksheets in a different layout. There are sometimes duplicated values in the column called Processing Data in the Raw Data sheet that which are used, along with a unique value in a field in the Raw Data sheet called meter, to populate the rows in the Report sheet.

    I've attempted to use a couple of loops to have the program capture the correct information for a given date value in the process date column and value in the meter column from the Raw Data sheet and place in the Report sheet.

    Although I'm a novice to Excel VBA now, I'd like to improve my skills so would greatly appreciate someone who could show me where I've gone wrong in the code. I've created a error handler in an attempt to trap the error, but not quite sure why the program is not able to complete successfully. I've been looking at this for a few days and have missed my first deadline and work with this, so I am grateful for any direction, advice, "two cents" in finding a resolution.

    Kind Thanks

    TDB
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel VBA Challenge (Excel 2003/)

    Welcome to Woody's Lounge!

    Without knowing the structure of the worksheets, it is impossible to tell how the code works. Apart from inconsistent indenting, some missing declarations, some superfluous lines and some code that could be made more efficient, the only obvious problem is that you haven't placed a line

    Exit Sub

    immediately above

    Err_Execute:

    so that the error message will always be displayed.

    If you need more help, please post (a stripped down copy) of the workbook.

  3. #3
    New Lounger
    Join Date
    Mar 2007
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel VBA Challenge (Excel 2003/)

    I've attached a sample of the file. The OrigReport sheet is the desired final product. I need to match on both Process Date and Meter from the Raw Data sheet and return corresponding row information in the Report Sheet. The values in Process Date need to match the dates in cells F2: AJ2 (which are populated from the input box capture and fill series lines in the routine). Conceptionally, I know its a pretty basic report. I'm having a bit of trouble executing this due to my lack of Excel VBA knowledge. I'm eager to learn because I've found this to be a really powerful tool.

    I appreciate the replying and identifying the number of problems. I'm off to work right now, but will make the modifications to the routine while I'm on my lunch break today.

    Many Thanks
    TDB
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel VBA Challenge (Excel 2003/)

    I'm afraid I didn't understand how your code works. I have attached a rather different version. The code is interspersed with comments.

    Note that cells aren't selected explicitly in the code. This is generally more efficient.
    Attached Files Attached Files

  5. #5
    New Lounger
    Join Date
    Mar 2007
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel VBA Challenge (Excel 2003/)

    Oh Wow! Obviously, I'm clueless about this subject! This made a big difference - Thanks. I used the code you have inserted it into my master file. I had to adjust the output columns and rows for my master sheet to get the volume values to adjust, but now I'm receiving an error and:
    A) The very first volume amount for the first date (11/1/06) and meter (A4CF4200.PV) is blank.
    [img]/forums/images/smilies/cool.gif[/img] The first row under (Output), the is placing the volume columns (F:AJ) one row below the the data in columns (A:E)
    C) The program errors after the value in B above are returned.

    I know I shouldn't quit my day job, but thought I could figure this out without any trainng and under a tight deadline (past the deadline, actually). I really do appreciate the great help.

    I read about the match method but wasn't sure how to use it - I know I needed something like a VLOOKUP so this is a great feature.

    Thanks so much,
    TDB

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel VBA Challenge (Excel 2003/)

    You've come quite far for someone just starting in Excel!

    Unfortunately, the code is tightly bound to the exact data structure. So I'd need to see the macro in the master file (or a stripped down copy of it) to know where it goes wrong.

  7. #7
    New Lounger
    Join Date
    Mar 2007
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel VBA Challenge (Excel 2003/)

    Here is my final workbook in the attached zip. The structure will not vary from this format. The Orig File shows the results I am trying to achieve. The Report shows missing value for the 11/1/06 for both the instream and outstream section. And the first line of the Outstream section only displays and the daily volume amounts are one row off.

    I think I'm understanding more of this as I work with it.

    Thanks!
    TDB
    Attached Files Attached Files

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel VBA Challenge (Excel 2003/)

    The workbook you originally attached didn't have separate Input Streams and Output Streams sections, so there was no way I could have provided for that. The code will have to be changed.

    I notice that the dates for a specific source node etc. are repeated. For instance, the range from 11/01/2006 through 11/28/2006 occurs three times for the first source node. How do you want to handle this? Should the data for the same date be added together, or averaged, or...?

    Please provide complete and accurate information, I cannot guess what you want to accomplish.

  9. #9
    New Lounger
    Join Date
    Mar 2007
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel VBA Challenge (Excel 2003/)

    Yes, you are correct. I apologize for the lack of
    clarity - I've been looking at this too long. There
    is repetition of dates in the list and here is why...

    The Source Node, Destination Node, Product,Product
    Code are related to an individual meter. They are
    classifications for the meter unit. For a given
    meter value listed in the Meter column, we are
    recording the volume of product by process date.

    The workbook contains meters for both the inflow and
    outflow of product. The top section of the report
    lists the inflow meters and the bottom section of
    there port lists the outflow meters. We are capturing
    this information so that we can pin point any
    inconsistencies with volume during the month for a
    given meter.

    Raw Data lists The Source Node, Destination Node,
    Meter, Product,Product Code information for each new
    processing date value.

    The purpose of the Report sheet is to display the info
    from Raw Data sheet so that each row represents a
    meter and it's corresponding The Source Node,
    Destination Node, Product,Product Code and Volume by
    Processing Date value.

    I've been overwhelmed by my lack of knowledge with VBA
    and the fact that the dates are inconsistently
    repeated. For Example one meter may corresponding
    records for the entire month, while another meter may
    not have had production on one or more days, while
    another may have produced only the last 15 days of the
    month.

    The only way I could think to ensure that I could
    transpose the data accurately from Raw DAta sheet to
    Report sheet was to key on the meter number and the
    processing date values.

    My first instinct was to create a pivot table report,
    but I am not performing any calculations by meter,
    only summing the totals for each processing date once
    the information is in the Report sheet. So once the
    informaton is in the Report Sheet layout, I can just
    add my totals for volume by processing date for each
    section (Inflows and Outflows) - I think I may know
    enough to create a macro to do this automatically,
    once the routine completes.

    The final product looks like OrigReport Sheet, which
    was produced from a manual process. We have 21
    workbooks (21 different units) with Raw Data Sheet and
    Report Sheet and I was trying to get a macro created
    to reduce the manual labor (and mistakes) in
    generating this report every month.

    I really appreciate the help - this is really going to
    help us be more efficient with generating this report
    each month.

    TDB

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel VBA Challenge (Excel 2003/)

    Don't worry - we'll get there in the end. But I still don't understand completely. Take ALKY BBFS ... for example. For 11/01/2006, there are three entries in the Raw Data Sheet: 20.648, 243 and 5.383. In the OrigReport sheet I see only the first one, 20.648 listed for ALKY BBFS ..., the others are for ALKY BBRAF ... and ALKY IC4 MU ... but I don't see those mentioned in the Raw Data sheet. So I don't know how a macro could produce those...

  11. #11
    New Lounger
    Join Date
    Mar 2007
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel VBA Challenge (Excel 2003/)

    Yes,

    Source Node, Destination Node, Product, and Product Code are assigned to a given meter. For a given value in the Meter column (the first meter is 4FC4208.PC) there are associated values in the Processing Date and Net Volume (as well as Net Mass, % Mass and API Gravity - which are not included in the Report sheet).

    The Source Node, Destination Node, Product, and Product Code and Meter Values are the same until there is a change in Value listed in the Meter Column.

    So the Net Volumes you mentioned (20.648, 243, and 5.383) reflect volumes transported by three different meters (4FC4208.PV, A4CF4200.PV ,A4F4203V.PV ) on 11/1/06.

    There should be one unique row for a given METER and PROCESSING DATE combination in both the output and input stream sections.

    I hope this is a bit clearer.

    Thanks so much - it's exciting to actually see the routine working (even if it needs a few adjustments)....it's embarassing to think about the time i spent on this (i think i need sign up for a class)

    TDB

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel VBA Challenge (Excel 2003/)

    I'm very sorry, but the values A4CF4200.PV and A4F4203V.PV are *not* present anywhere in the Raw Data sheet, so there is *no* way a macro can produce a report such as that in OrigReport. Macros can do a lot, but they cannot conjure up values out of nothing. Are you sure you provided a correct version of the Raw Data?

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel VBA Challenge (Excel 2003/)

    I've tried to recreate OrigReport as best I could, but as I mentioned in my previous reply, the macro cannot fill in some data in columns B through E since they are not present in the Raw Data.
    Unfortunately, the code has become very convoluted, since it has to take far more situations into account than in your first sample.
    See attached workbook (zipped)
    Attached Files Attached Files

  14. #14
    New Lounger
    Join Date
    Mar 2007
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel VBA Challenge (Excel 2003/)

    Hans,
    I'm an idiot... .Somehow my previous attempts at the macro must have wiped out values in the Raw Data Report. I saved the original and attached Alky Nov MTD - final2.zip. All the values on Raw Data do coincide with Report (OrigReport) sheets. No wonder it was so confusing. The information on Raw Data sheet is replicated in the Report sheet but transposed so that each Processing Date is in a column. There should be a unique Meter value in the list.
    Attached Files Attached Files

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel VBA Challenge (Excel 2003/)

    The macro from my previous reply should work with one modification - in the previous version of Raw Data, the data began in row 6, now in row 4, so you must change the line

    For rS = 6 To rM

    to

    For rS = 4 To rM

    The rest can remain as it is.

Page 1 of 2 12 LastLast

Posting Permissions

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