Consulting

Results 1 to 14 of 14

Thread: Create sophisticated loop

  1. #1

    Exclamation Create sophisticated loop

    Dear guys,

    im currently struggling with creating a vba code for the problem displayed:

    Please create a temp_sheet, transfer the values and formatting from the first sheet found with the prefix "raw_" and apply a text filter in the temp_sheet.
    Find the selected range in sheet "table 1" in column A and use the neighboring values in column B as filter criteria in the text filter in column B of the temp_sheet.

    table 1 looks like this (user selects all File A cells)

    Column A Column B
    File A 454/456zzuuCd/452
    File A 100-100
    File A Advc9873
    File B 4545

    then the temp_sheet should look like this

    Column A Column B Column C 24-06-24 24-06-25 24-06-26 24-06-27
    SheetB 454/456zzuuCd/452 Travel 0,5
    SheetB 100-100 Training -1,7
    SheetB Advc9873 Travel 8

    In the next step, i want the content of the cells (starting from column B and row 2) to be inserted to the sheet, whose name is in column A. When inserting it to the respective sheet, i want the correspondending column header to be considered, because the arrangement of columns of temp_sheet and e.g. SheetB deviates from another.

    keep in mind, that
    1) table 1: the selected range in table 1 can consider out of more then >50 cells
    2) table 1: the structure of neighboring values in column B can deviate in terms of string (number/values/letters)
    3) temp_sheet/SheetB: the amount of columns can be more >100 columns, but i want everything to be considerd, if the criteria matches
    4) in some cases, temp_sheet can have columns headers, that dont exist in SheetB. Those columns should be skipped.
    5) once the values have been successfully transmitted into SheetB, i want temp_sheet to be cleared and the content of "raw_" sheet no.2 to be inserted and to repeat everything like with the first "raw_" sheet. I want to process all "raw_" sheets, one by one.

    Can someone help me?
    Thank you very much!!

  2. #2
    VBAX Mentor
    Joined
    Nov 2022
    Location
    The Great Land
    Posts
    348
    Location
    If you have struggled with this, what code do you have so far?

    Might help to provide file for analysis.
    How to attach file: Reading and Posting Messages (vbaexpress.com), click Go Advanced below post edit window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Unfortunately, my VBA knowledge is very poor, which is why I cannot create any code on my own. The tables look like the ones described in the post, except that they have more rows and more columns with date values. If you could help me, I would be extremely grateful!

  4. #4
    VBAX Mentor
    Joined
    Nov 2022
    Location
    The Great Land
    Posts
    348
    Location
    I am mainly an Access user. Don't code much in Excel.
    Whatever you are doing in Excel, I would probably do in Access.

    So you haven't even attempted anything. Not much of a struggle.

    I am not interested in building a workbook. If you can't bother to provide existing file, my incentive to help is fading.
    How to attach file: Reading and Posting Messages (vbaexpress.com), click Go Advanced below post edit window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Quote Originally Posted by June7 View Post
    I am not interested in building a workbook. If you can't bother to provide existing file, my incentive to help is fading.
    Fair point! I've attached the Workbook. I'm looking forward to seeing your solution. Thank you very much in advance.
    FYI: keep in mind, that the name of the source sheet / string after raw_ can vary. I could be also named "raw_MasterC"


    Quote Originally Posted by June7 View Post
    I am mainly an Access user. Don't code much in Excel.
    Whatever you are doing in Excel, I would probably do in Access.
    Due to various circumstances, I have to stick to excel.
    Attached Files Attached Files
    Last edited by Sunny007; 06-24-2024 at 01:27 AM.

  6. #6
    VBAX Contributor
    Joined
    Nov 2020
    Location
    Swansea,South Wales,UK
    Posts
    103
    Location
    Sounds like homework to me?

  7. #7
    Quote Originally Posted by Gasman View Post
    Sounds like homework to me?
    No, it's not homework. What school would give its students such complex tasks? Anyway, I don't understand what you're trying to tell me?

  8. #8
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,746
    Location
    Quote Originally Posted by Sunny007 View Post
    No, it's not homework. What school would give its students such complex tasks? Anyway, I don't understand what you're trying to tell me?
    The rules of the forum don't want the members to DO any homework for students, but only to HELP or SUGGEST ways for students to do it for themselves so that they will learn.

    Sometimes we get questions that are almost like "Do #7 - Write a macro that wil ....". You get the idea

    Sometimes the question is a little blurred

    Not homework .. fair enough
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  9. #9
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,746
    Location
    1. The pivot table is from "'\Users\BezabihEmanraw_SheetB!$GB$1684uel\Desktop\BP Überarbeitung\[workbook.xlsm]BP_WBS_export'!$A:$F"

    2. 'RawSheetB has Col A added. I assume that a manual add

    3. SHeet B has ...

    Column A Column B Column C Column A Column B Column C Column A Column B Column C Column A Column B Column C WBS / Order number=

    twice, then a bunch of Act dates and then a bunch of FC dates (Actuals and Forecast?)

    "SheetB" and "External' in 2 columns

    WBS and the dates I get, but what goes in the other columns are a mystery.

    4. How do you tell ACT from FC?

    5. "I want to process all "raw_" sheets, one by one." -- everything into one worksheet or seperate ones.

    It might be easier to just start with your export sheet / file(s) (probably a CSV??) and format your output, when needs better definition
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  10. #10
    Hey Paul,
    thank you for your comment.
    1. please ignore the link. The source data is a list. the pivot is simply a support to get the data into the right shape (for the final target-sheet) -> like cross table. The Temp_sheet is a support to filter the values based on the criteria mentioned above (because you can not apply a textfilter on a pivot table).
    2. correct, it was inserted manually
    3. the header in row 2 in the temp_sheet is corresponding to the header in row 2 in the target sheet, ignore row 1 in both sheets
    4. Its not necessary to differentiate between ACT and FC when transfering the data, so please ignore the line one in the target sheet
    5. seperate ones. Currently, there is only one raw sheet. But in the future there could be more raw_ sheets, and the all should then be processed (each of them in a empty temp_sheet)

  11. #11
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,746
    Location
    I'd like to start with a file of the actual rawest input data, say 100-200 rows with a variety of variables
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  12. #12
    VBAX Mentor
    Joined
    Nov 2022
    Location
    The Great Land
    Posts
    348
    Location
    Looked at workbook.
    I do not understand your stated requirements.
    I do not understand SheetB. Why does it not horizontal scroll?

    Need someone more adept at manipulating Excel to figure this out.
    How to attach file: Reading and Posting Messages (vbaexpress.com), click Go Advanced below post edit window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    This Post can be closed, i've implemented an easier solution on my own. Thanks anyway

  14. #14
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,183
    Location
    Hmmm... What solution did you arrive at? It would be somewhat educational given the initial inquiry was a little vague and those people trying to assist where clearly restricted by a baffling worksheet.
    layout description. For future reference, since we cant see over your shoulder at your workbook, either supply a sample workbook or be very descriptive when first asking for assistance. The fact that we are 14 posts into the thread and people still don't understand the issue is clearly indicative that the query is poorly explained or badly layout.

    We would love to assist you, but you need to be a little helpful yourself.
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

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
  •