View Poll Results: Macro challenge! how to pull information across with certain limitations

Voters
5. You may not vote on this poll
  • Can be done

    4 80.00%
  • Impossible

    0 0%
  • Possible if given basic templates

    0 0%
  • Difficult even if had templates

    1 20.00%
Page 2 of 2 FirstFirst 12
Results 16 to 24 of 24
  1. #16
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    You still haven't answered my questions. If you want help with this problem from me, I need to understand what you are trying to do.

    I ask again, please walk me through the details of what you want done. Please go through at least one complete loop and describe any deviations (eg when there is a match and when no match). E plain what you want to count, what you are comparing, what defines a "match" etc.

    If you want help with existing code, please explains the lines that don't work and how you want them modified and then I won't worry about the overall process.

    Steve

  2. #17
    New Lounger
    Join Date
    Jul 2014
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I am looking for one excel workbook (Master Profile) to be updated from another excel workbook (Master Load File) by using a Macro.

    In the Master Profile workbook i have attached you will see that in column FP i have already put in unique code. This is combining the WBS Code, Resource Type and Total Man-hours to provide a unique lookup code. In the Master Load File i have similar coding of WBS Code, Resource Type and Total Man-hours to Feb14. This allows for a match calculation to be done (and is already shown in column FQ of the Master Profile). All the unique codes will line up as the source file (Master Load file) does not change historical data so itís always stable.

    If look in Column FQ of the Master Profile and look at the match formula you will notice it jumps from 15 to 17 as line 16 is missing. I need the Macro to first check the Master Load file for code A.10.01 for this worksheet. Once it has a count of how many lines there is for that code to check against the Master Profile sheet and for any misalignment to insert new lines where required. Any new lines which are inserted into the headcount block have to be replicated in the following blocks below Utilization, Man-hours, Rates.

    After any new lines that might be required have been inserted the Macro has to copy the formatting from Feb14 (column BL) and replicate the format for the next 3 months. Once that has been done, the Macro will then transpose the man-hours from the Master Load File into the block titled Man-hours (rows 262-383 for now). If possible the Macro will backfill any calculations in the following blocks Headcount, Utilization, Rates from the new information.

    Once those parts are done the Macro will need to replicate the above for part for the Labour Cost and Material Cost blocks with any new lines being inserted and the information from the Master Load File transposed over.

    If the Macro works it will produce an outcome similar to Master Profile v2 where i had manually done those steps and give an example of what i am looking completion. Thatís if itís possible to be done by Macro.

    I have done a self-recorded macro doing some of the steps, but struggle with how to get it be setup to do any IF and Lookups. I can provide a copy of the Macro i have done if this might be easier for you to modify or understand easier what is being done.

    Stuart
    Attached Files Attached Files

  3. #18
    New Lounger
    Join Date
    Jul 2014
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro Possibilities: Meet criteria in one workbook to pull information from another

    Hi All,

    I have a Workbook which is attached called Master Profile.
    I have in Column FP created a unique code which is used to look up and match onto another workbook.
    In Column FQ i have shown where the codes line up and the Match to the workbook called Master Load File (also attached).
    In the worksheet titled check i have already done a count function which shows total number of cells in A.10 and the test, as well as the current date information is to, and when itís to be extended too.
    I am looking for a Macro which can perform the following steps.

    First part is for hours
    1) to look up the worksheet titled check and see if the count function already there matches up.
    2) to go to worksheet and if the check did not match to look at column FQ to see what row is missing against the workbook Master Load File and worksheet titled hrs.
    3) then insert new row into the blocks titled headcount, utilization, man hours, rates. As well as doing this to replicate any format or calculations in the blocks
    4) using the check sheet again to see what date information was last too and the new proposed date.
    5) to copy and paste format from the date above to the new proposed date
    6) in the man-hours block to load in the information for the proposed time period into the lines from the hrs worksheet in workbook Master Load File.

    Second part is for costs
    similar to above apart from looking at the worksheet called costs and doing the above steps .

    The solution i am looking for is the outcome in Master Profile v2 which i have manually done and looking for the macro to produce this outcome.
    Hopefully this can be done or if not any ideas on ways to do this process without manually doing it every time
    Attached Files Attached Files
    Last edited by stuarteng81; 2014-07-15 at 12:41. Reason: attachments

  4. #19
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    How is this different from the thread at http://windowssecrets.com/forums/sho...n-limitations?

    Should I stop working on that thread and start on this instead?

    Steve

  5. #20
    New Lounger
    Join Date
    Jul 2014
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Please keep working on this. I don't know if the attachment in this one is better as it has been suggested using the date function to help pull just select information through than all of the information.

  6. #21
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,415
    Thanks
    208
    Thanked 836 Times in 769 Posts
    Steve,

    I merged the threads.
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  7. #22
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    You are making it very difficult to understand, let alone work on a solution to your project. You keep changing the format and the requirements and you don't answer the questions. I am about ready to just drop it and let someone else try to "pull teeth".

    I think I have mentioned this before, but your master profile file is still corrupt in some manner and has unreadable content in it when I try opening it in XL2010. Not sure how the repaired file matches your original.

    The Master Load is not going to be changed in this, but the Master Profile will be and the form it is in will be the start for the macro. The worksheet named "Check Sheet" in this workbook is new. What is this and where does it come from? Did you forget to add it in the all the previous versions, or is this not supposed to be in the start file, but is supposed to be generated by the macro. If it is supposed to be generated please identify the logic.

    The formulas in FP and FQ were not in all the samples. Will they be generated pre-macro f the macro to use or are they supposed to be macro generated? If macro generated what is the logic to them? FP uses 2 values in the row of the formula, the last value from a completely different row of the workbook. How would the macro know what row to use I presume that it will not always be 258 rows from the current row.

    The formulas in FQ are not linked to the Master load file but a completely different file. Is there another file in play here or is the formula wrong? What is the correct formula? If it is meant to refer the Hrs sheet in the Master load file, there is nothing in column AH so none of the matches will give row values.

    Could you walk me through how check sheet is supposed to be used. I can not follow your logic. If this sheet is supposed to be generated at runtime by the macro, do you need to create the sheet? Perhaps there are ways (I would have to understand what you ultimately want the code to do before knowing) without creating the temporary sheet.

    Some questions on what you want:
    1) to look up the worksheet titled check and see if the count function already there matches up.
    Look up what exactly? What is supposed to match up? Is this a onetime lookup or is there some looping involved with multiple comparisons?

    2) to go to worksheet and if the check did not match to look at column FQ to see what row is missing against the workbook Master Load File and worksheet titled hrs.
    Go to what worksheet. Will there be only 1 row missing? Again elaborate, I have asked before, of you are looping walk me through a couple cycles in detail, not in generalities.

    3) then insert new row into the blocks titled headcount, utilization, man hours, rates. As well as doing this to replicate any format or calculations in the blocks
    Insert a blank row or copy and insert a row with content? Does it matter where in each of those blocks the row is inserted? At runtime, how does the macro know where the blocks are located? If you insert rows they will be changing location.

    4) using the check sheet again to see what date information was last too and the new proposed date.
    How is this supposed to be done exactly?

    5) to copy and paste format from the date above to the new proposed date
    I don't understand the point of this. I don't see a different in the date formats you have on the check sheet. They are both mmm-yy

    6) in the man-hours block to load in the information for the proposed time period into the lines from the hrs worksheet in workbook Master Load File.
    Could you elaborate on what you mean by "load in the information", and what "lines" exactly you want to get, and where in the Master profile book you want to put them.

    Until can explain the process to us in words, I don't see how a macro can be created. The macro needs the gory details to be able to function. The logic must be programmed into it to be able to adapt to changes in the master profile book as well as the Master load file book. You seem unable to even explain the process with a particular file. In fact you seem to keep changing the file we are meant to work with.

    Steve

  8. #23
    New Lounger
    Join Date
    Jul 2014
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Steve,

    Sorry this is proving so hard but due to the nature of the project i am very unsure how much information can be freely shared openly. I will try and answer your queries and hopefully get this sorted.

    Firstly hoping the new attached Master Profile will open without having to be repaired. The check sheet was included as an afterthought by the last user as a way to help the macro perform better. The sheet is purely there to allow the macro to cross check which worksheets would need new lines required as well as the date range to be looking at.

    The information in Master Profile columns FP & FQ are generated by me and not by the macro. This is to solve the macro having to always try link up with the man-hours for the last bit of the information for the code in column FP. I have corrected the match function in FQ to the Master Load File.

    As for your questions i will answer them the now.

    1) The check sheet is created looking at the number of lines matching up in A.10.01 against the Master Load File and seeing if they match up. As can see there is multiple worksheets and the majority align. This function and sheet is updated manually by me and only there to refer that all worksheets and workbooks match.
    2)A.10.01 is the worksheet I was referring to. I am looking for the macro to look in the check sheet to see if a new line is required, if so to look in column FQ and see what row is missing from the Master Load file. In the example attached you will see in Column FQ it jumps from 15 to 17 showing a line will be required to be inserted.
    3) I had assumed since the blocks in column A has the titles it could be looking up that reference for the blocks to then insert new rows within. I have in other macros used this to pull information from these templates.
    4) To save having to always pull across all information. I had thought if knew the date of the last point of information and the date to be extended to it could be linked into the macro. Reasoning for this although the cycle is meant to be every 3months it can at times vary to be more or less. So if able to link it to the date function this would solve information being excluded or too much pulled through.
    5) The dates are moving from February 2014 to May 2014. If this works the start point next cycle would be May 2014 to September 2014. The format in column BL is certain blocks are shaded and others not. This is to allow information to be only entered into the unshaded cells hence copying the format over the time period.
    6)The information aligns up to February 14 and what i need the macro to do is pull in the months March 14 to May 14. These will line up with the match function to make sure the correct information is inserted into the correct lines. The man-hours should be placed into the block titled man-hours (starting row258 although will move once new rows inserted).

    Regards
    Attached Files Attached Files

  9. #24
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    I don't see the issue as having anything to do with the proprietary nature. This could be solved easily enough by creating a representative example with dummy data in it. The problem I continue to have is understanding the setup since it seems to me to structured very poorly with a hodge-podge of actual information interspersed with formulas and the "linked data" (data referring to the same item) not collected into one location.

    But even that can be worked around to some degree, If I understood what you wanted to the code to actually do.

    You seem to have fixed the corruption which is good. But I still don't understand the 'Check sheet' worksheet and how the macro would be using this.

    As to Master Profile workbook, worksheet A.10.01, col FQ. So I can see, these are row numbers and some are missing. (row 1 is the header so we ignore that one missing).

    So the code could go through col FG and find that row 16 is missing in the headcount section. So once it finds this what do you want the code to do?

    Following that, it would find rows 11, 12, 25, 26, 30, 31,32, 33, 34, 35, 36, and 37 missing in the Labour Costs section. When it finds each of these what do you want the code to do? Should the code be also be reacting to the fact that this section is working with rows >23 when the headcount section only went to row 23?

    Then it would come to the the material costs section. Does it start with row 11 for checking or should it react to the fact that rows 2-10 are missing? If so what should it do in each of those cases? In addition the inclusive rows 13-25, 27-29, and 37 missing. What should it do for each of these cases? Again does the code need to react to the fact that there are different rows missing than in the other sections?

    Please walk me through a few examples 16 is missing in the headcount section, 11, and 37 missing in the Labour Costs section, and perhaps, row 2, 13, and 37 in the material costs section?

    Until I can understand the process, I can't do any work on a macro...

    Steve

Page 2 of 2 FirstFirst 12

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
  •