+ Reply to Thread
Results 1 to 10 of 10

Loop through and copy text from one cell to a cell in another column based on date match

  1. #1
    Registered User
    Join Date
    03-16-2012
    Location
    MN
    MS-Off Ver
    Excel 2010
    Posts
    47

    Loop through and copy text from one cell to a cell in another column based on date match

    I use conditional formatting to highlight cells in this Gannt chart. I want to put the Task Description in the first highlighted cell for the date range.

    You can see how I am doing the conditional formatting, using the start and end dates, I compare the first day of the Month in the start date field and the last day of the Month in the end date field against the date in row 2.

    What I want to do it, for each record, figure out which column matches the start date, and put the Task Description in that field.

    I am attaching a sample document that first shows what I start with, and what I want to end up with.
    Attached Files Attached Files
    Last edited by James Keuning; 01-20-2021 at 07:26 PM.

  2. #2
    Forum Guru
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,854

    Re: Loop through and copy text from one cell to a cell in another column based on date mat

    You can just use formula (taken from your conditional formula, marked with red, and add some more conditions) like this :
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    and if you want to use VBA so the formula in cell is wipe out (get value only) :
    Please Login or Register  to view this content.

  3. #3
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,558

    Re: Loop through and copy text from one cell to a cell in another column based on date mat

    Another formula

    D4
    =IF(AND($B4<>"",MONTH($B4)=MONTH(D$2)),$A4,"")
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  4. #4
    Forum Guru
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,854

    Re: Loop through and copy text from one cell to a cell in another column based on date mat

    AlphaFrog,

    Very nice and short formula indeed, but probably there is a reason why OP choose that lengthy formula, for example there could be a chance that the table is still going right (for next years to come), so cell P2 = 01/01/2022, Q2 = 01/02/2022, need one more condition (e.g. And(month..=month, year.. = year..).
    Last edited by karedog; 01-20-2021 at 04:06 AM.

  5. #5
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,558

    Re: Loop through and copy text from one cell to a cell in another column based on date mat

    Quote Originally Posted by karedog View Post
    ...but probably there is a reason why op choose that lengthy formula, for example there could be a chance that the table is still going right (for next years to come), so cell p2 = 01/01/2022, q2 = 01/02/2022, need one more condition (e.g. And(month..=month, year.. = year..).
    d4
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by AlphaFrog; 01-20-2021 at 04:20 AM.

  6. #6
    Registered User
    Join Date
    03-16-2012
    Location
    MN
    MS-Off Ver
    Excel 2010
    Posts
    47

    Re: Loop through and copy text from one cell to a cell in another column based on date mat

    The reason the formula does not work is that it prevents the text in the cell from flowing out into the adjacent cell. Since these descriptions are longer than the cells, the adjacent cells need to be empty.

  7. #7
    Registered User
    Join Date
    03-16-2012
    Location
    MN
    MS-Off Ver
    Excel 2010
    Posts
    47

    Re: Loop through and copy text from one cell to a cell in another column based on date mat

    The reason a formula won't work is that it makes it so that the adjacent cell is not empty, so the display of the text gets truncated. That's why I am thinking of vba to wipe the non-needed formulas.

  8. #8
    Registered User
    Join Date
    03-16-2012
    Location
    MN
    MS-Off Ver
    Excel 2010
    Posts
    47

    Re: Loop through and copy text from one cell to a cell in another column based on date mat

    Quote Originally Posted by karedog View Post
    You can just use formula (taken from your conditional formula, marked with red, and add some more conditions) like this :
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    and if you want to use VBA so the formula in cell is wipe out (get value only) :
    Please Login or Register  to view this content.
    YES! This VBA is perfect. I will study it!

  9. #9
    Registered User
    Join Date
    03-16-2012
    Location
    MN
    MS-Off Ver
    Excel 2010
    Posts
    47

    Re: Loop through and copy text from one cell to a cell in another column based on date mat

    Quote Originally Posted by karedog View Post
    You can just use formula (taken from your conditional formula, marked with red, and add some more conditions) like this :
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    and if you want to use VBA so the formula in cell is wipe out (get value only) :
    Please Login or Register  to view this content.
    In trying to modify this for my production data I noticed that the vba function breaks if the duration is longer than two months. I am trying to understand what the offsets are doing and I just can't crack it.

    EDIT: I figured out why it was breaking for longer durations, and that's because it was looking at the EOMONTH for the end date, and it should look at the start date, since I only what the value in that first month's cell.

    But what I now cannot figure out is how this needs to be modified in my production data, where I have more cells to the left of everything, so the range starts at I3. I tried to methodically change the offsets to understand how they reference other cells with that column reference combination, but for some reason I just do not understand.

    For example, you have COLUMN(D3)=4, what is the significance of that? Because that is always true. Isn't it? I don't understand what this accomplishes, even though it obviously works!
    Last edited by James Keuning; 01-23-2021 at 01:40 PM.

  10. #10
    Forum Guru
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,854

    Re: Loop through and copy text from one cell to a cell in another column based on date mat

    Hi James, many thanks for rep.points given.

    Yes I missed to check that there could be more than 2 months span occurred.
    If you want to use my style (using your conditional formatting formula, as part of the cell formula), you can change the code to this :
    Please Login or Register  to view this content.
    Or, if you want to use AlphaFrog's formula, which is obviously very short, then you can change the code to this :
    Please Login or Register  to view this content.
    As for your question :
    Quote Originally Posted by James Keuning View Post
    For example, you have COLUMN(D3)=4, what is the significance of that? Because that is always true. Isn't it?
    It is true if the current cell is a cell located on column D, but the formula is copied across the whole range (column D:O), so for cells of another column (E:O), it is false.

    To have a better picture, please try this :
    - On a newly created sheet, on cell A2 put this formula =A1+1
    - Now copy this formula to enlarged range, for example to range A2:C4
    - The formula on cell B2 becomes =B1+1, on A3 becomes =A2+1
    - In other word, Excel will self adjusted the formula on header range (in this case is the single cell A2, but in general, not necessary a single cell, it could be row or column also) to entire range.

    The same thing applied to the VBA code, we input only the formula on the top left cell, and Excel will self adjusted the formula to the whole range.

    You can examine this behavior by delete this code line :
    Please Login or Register  to view this content.
    so the code becomes :
    Please Login or Register  to view this content.
    and now if you go back to the sheet, you can examine the self adjusted formula by Excel.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Copy Cell From One Cell and Paste to next blank cell, Loop until last row/column
    By Beginner Level in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-01-2020, 02:38 AM
  2. [SOLVED] Loop through column, match date, copy & paste row data to another sheet
    By benlw1984 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-15-2020, 06:08 AM
  3. [SOLVED] Loop through Column and Copy/paste if cell = text
    By excelnovice128 in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 11-02-2018, 02:07 PM
  4. [SOLVED] Loop until cell values match then copy, move to next cell and loop and copy again
    By potga in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-11-2016, 08:51 PM
  5. [SOLVED] How to Copy a cell to other column based on the date on the same row?
    By nunofrcds in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 08-12-2016, 10:56 AM
  6. copy cell values to another sheet based on text in a column
    By manteca in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-13-2014, 09:46 AM
  7. Replies: 1
    Last Post: 07-01-2011, 07:21 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1