1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

get data from cell depending on dates from array of cells

Discussion in 'Ask an Excel Question' started by jmendenhall22, May 24, 2017.

  1. jmendenhall22

    jmendenhall22 New Member

    Messages:
    15
    I have todays date in a1
    I have names in b3 to b25 (not every cell will always have a name, can be empty)
    I have dates spread throughout cells F3 to AZ25, also have cells with initials and blank cells. (All cells in array have formulas in them.)
    On sheet2 I want to compare todays date (A1) with (sheet1) dates F3:AZ25 in array of cells and if they match then get name from B3:B25 and place in column (B) sheet2. This may have anywhere from 0 to 25 names.
    I would like the returned names to fill in row b3 to b25 with no empty cells between names.
    Is this possible??
    I attached the workbook
    Thank you for your time.

    Attached Files:

  2. AlanSidman

    AlanSidman Active Member

    Messages:
    164
    try this:
    Code (vb):

    Option Explicit

    Sub mendenhall()
        Dim s1 As Worksheet, s2 As Worksheet
        Set s1 = Sheets("SMMP Tracking Sheet")
        Set s2 = Sheets("Sheet2")
        Dim lr As Long, r As Long
        Dim c As Range
        Dim rng As Range
        Set rng = s1.Range("F3:AZ25")
        For Each c In rng
            lr = s2.Range("B" & Rows.Count).End(xlUp).Row
            If c.Value = s2.Range("A1") Then
                r = c.Row
                s2.Range("B" & lr + 1) = s1.Cells(r, 2)
            End If
        Next c
        For Each c In rng
            lr = s2.Range("C" & Rows.Count).End(xlUp).Row
            If c.Value = s2.Range("A1") + 1 Then
                r = c.Row
                s2.Range("C" & lr + 1) = s1.Cells(r, 2)
            End If
        Next c
        MsgBox "complete"
    End Sub
    Chirag R Raval likes this.
  3. jmendenhall22

    jmendenhall22 New Member

    Messages:
    15
    I am uneducated in excel, where do I place this. I am sure not in a cell.
    I opened VBA and added module in sheet 2 but do not know how to get it to run
  4. AlanSidman

    AlanSidman Active Member

    Messages:
    164
    How to install your new code
    1. Copy the Excel VBA code
    2. Select the workbook in which you want to store the Excel VBA code
    3. Press Alt+F11 to open the Visual Basic Editor
    4. Choose Insert > Module
    5. Edit > Paste the macro into the module that appeared
    6. Close the VBEditor
    7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

    To run the Excel VBA code:
    1. Press Alt-F8 to open the macro list
    2. Select a macro in the list
    3. Click the Run button
  5. jmendenhall22

    jmendenhall22 New Member

    Messages:
    15
    Thank you very much, I got it and it works great
    Chirag R Raval likes this.
  6. AlanSidman

    AlanSidman Active Member

    Messages:
    164
    Thanks for the feedback. Good luck with your project.
    Chirag R Raval likes this.
  7. bosco_yip

    bosco_yip Well-Known Member

    Messages:
    1,247
    Another option in formula way,

    In "Sheet2" B3, formula copy down :

    =IFERROR(INDEX('SMMP Tracking Sheet'!$B$3:$B$25,AGGREGATE(15,6,(ROW('SMMP Tracking Sheet'!$B$3:$B$25)-ROW('SMMP Tracking Sheet'!$B$2))/('SMMP Tracking Sheet'!$F$3:$AZ$25=$A$1),ROWS($1:1))),"")

    Regards
    Bosco
  8. jmendenhall22

    jmendenhall22 New Member

    Messages:
    15
    Both work great and thank you. Additionally I would like to get the name out of the corresponding row at the top of the column with the date as well. I have attached the file with what I would like sheet to be.
    Thanks for looking

    Attached Files:

    Chirag R Raval likes this.
  9. AlanSidman

    AlanSidman Active Member

    Messages:
    164
    Amended the code to reflect your request:

    Code (vb):

    Option Explicit

    Sub mendenhall()
        Dim s1 As Worksheet, s2 As Worksheet
        Set s1 = Sheets("SMMP Tracking Sheet")
        Set s2 = Sheets("Sheet2")
        Dim lr As Long, r As Long, x As Long
        Dim c As Range
        Dim rng As Range
        Set rng = s1.Range("F3:AZ25")
        For Each c In rng
            lr = s2.Range("B" & Rows.Count).End(xlUp).Row
            If c.Value = s2.Range("A1") Then
                r = c.Row
                x = c.Column
                s2.Range("B" & lr + 1) = s1.Cells(r, 2) & s1.Cells(2, x)
            End If
        Next c
        For Each c In rng
            lr = s2.Range("C" & Rows.Count).End(xlUp).Row
            If c.Value = s2.Range("A1") + 1 Then
                r = c.Row
                x = c.Column
                s2.Range("C" & lr + 1) = s1.Cells(r, 2) & s1.Cells(2, x)
            End If
        Next c
        MsgBox "complete"
    End Sub
     
    Chirag R Raval likes this.
  10. jmendenhall22

    jmendenhall22 New Member

    Messages:
    15
    I am trying to get this info for 5 days now and when I add
    Code (vb):

    For Each c In rng
            lr = s2.Range("D" & Rows.Count).End(xlUp).Row
            If c.Value = s2.Range("A1") Then
                r = c.Row
                x = c.Column
                s2.Range("D" & lr + 1) = s1.Cells(r, 2) & s1.Cells(2, x)
            End If
        Next c
        For Each c In rng
            lr = s2.Range("E" & Rows.Count).End(xlUp).Row
            If c.Value = s2.Range("A1") + 1 Then
                r = c.Row
                x = c.Column
                s2.Range("E" & lr + 1) = s1.Cells(r, 2) & s1.Cells(2, x)
            End If
    For Each c In rng
            lr = s2.Range("F" & Rows.Count).End(xlUp).Row
            If c.Value = s2.Range("A1") Then
                r = c.Row
                x = c.Column
                s2.Range("F" & lr + 1) = s1.Cells(r, 2) & s1.Cells(2, x)
            End If
        Next c
        For Each c In rng
            lr = s2.Range("G" & Rows.Count).End(xlUp).Row
            If c.Value = s2.Range("A1") + 1 Then
                r = c.Row
                x = c.Column
                s2.Range("G" & lr + 1) = s1.Cells(r, 2) & s1.Cells(2, x)
            End If
     
    I error for control value already in use. What am I doing wrong
    Last edited by a moderator: Jun 19, 2017
  11. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,579
    Hi ,

    Are you sure the code you have posted is the complete code ?

    The last For ... Next loop has its Next statement missing.

    What is the statement which is highlighted when the error is generated ?

    Narayan
    Chirag R Raval likes this.
  12. AlanSidman

    AlanSidman Active Member

    Messages:
    164
    Also the second For each statement is missing a Next command.
  13. jmendenhall22

    jmendenhall22 New Member

    Messages:
    15
    Sorry, I have not been on for awhile. I figured it with your help of course, thanks.
  14. jmendenhall22

    jmendenhall22 New Member

    Messages:
    15
    Is it possible to use the output data from the above to place into a monthly calendar so I can use it for assignments for staff? I know I will have to modify the original code to cover more days but I need it to auto update bi-weekly calendar.

Share This Page