1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Very hard macro in Excel (copy part of one cell to several areas with logic)

Discussion in 'Business Applications' started by NextGen, Jun 8, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. NextGen

    NextGen Thread Starter

    Joined:
    Jun 8, 2011
    Messages:
    3
    Hello everyone,

    I am completely stumped and I hope to find some help from someone here... I am trying to format an excel spreadsheet that has over 10k records is a very strange format. Please see attachment...

    I have created the logic to grab the numbers to the left of the "carrot" in cell E3 and replicate that number in cells B3 B4 B5 B6 B7 B8 B9 B10. That process is replicated to pull the numbers to the left of the "carrot" in cell E13 and replicate that number in cells B13 B14 B15 B16 B17 B18 B19 B20...And so on and so fourth. The problem I am stumped on is how do i replicate this process all the way down the spreadsheet?

    One FYI is that each record "bock" is not always (as in the example spreadsheet) 10 lines it can be 11/12/13 or more lines. Basically the record starts at the letters "MSH" (see A1) So I the logic I am thinking of is to push the code in red in the "E" cells to the "B" column after the "EVN" (in cell A2) and to stop before the "MSH" (in cell A11) and continue that process down the spread sheet.

    I hope this is not too confusing but I have been banging my head on the wall for hours and just cant figure it out. Thank you all for your help
     

    Attached Files:

  2. CDHarm

    CDHarm

    Joined:
    May 26, 2011
    Messages:
    255
    NextGen,

    How does column "A" get populated? Will there be empty cells in it between the first occurrence of "MSN/EVN"?
    Code wise it should not be hard to get the "Number" to the left of the carrot. The problem comes up in populating it to column A not knowing if its populated with or with out blanks.
     
  3. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,576
    First Name:
    Hans
    In regulr Execl formula you use (Example is Clumn \

    In your sample for Row 3

    F3=FIND("^";E3;1)
    this formula will retrun 5

    This indicates that the ^ is in position 5
    If the value returned is 1 then there is nothing before the ^ so no number.


    Now the problem.

    If there is no ^ in the cell then you will get aan error so the correct formula will have to be:


    =IF(NOT(ISERROR(FIND("^";E3;1)));FIND("^";E3;1);"")

    If you want to do this in a macro, then the formula is


    IF instr(1,"^",cellvalue) > 1 then
    your code
    End If

    So all you have to do is start with Row 1 'til the laste row and check column E
    and fill the number in column B

    You'll have to define a rule to fill all of column B until something triggers the change and I think you are the one that knows which criteria is to be used.
     
  4. CDHarm

    CDHarm

    Joined:
    May 26, 2011
    Messages:
    255
    Hi,

    Here's a copy that will populate the data to column "B". The code is based on the current format of the work sheet. If the format changes the code will not work properly. You may need to change the sheet names. I used "Sheet1" per your example.
     

    Attached Files:

  5. NextGen

    NextGen Thread Starter

    Joined:
    Jun 8, 2011
    Messages:
    3
    Thank you guys for the help, got it all to work thanks to you!
     
  6. NextGen

    NextGen Thread Starter

    Joined:
    Jun 8, 2011
    Messages:
    3
    CDHarm, thanks for the attachment as well was extremely helpful!
     
  7. CDHarm

    CDHarm

    Joined:
    May 26, 2011
    Messages:
    255
    Thanks for letting us know.
    If your satisfied please show this thread as "Solved"
     
  8. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Similar Threads - Solved Very hard
  1. cgeorgianni
    Replies:
    1
    Views:
    256
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1001242

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice