• 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...

  • 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

J

jmendenhall22

New Member
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.
 

Attachments

  • SMMP tracking sheet3.xlsx
    22.2 KB · Views: 11
try this:
Code: 
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
 
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
 
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
 
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
 

Attachments

  • Copy of SMMP tracking sheet5.xlsx
    22.4 KB · Views: 5
Amended the code to reflect your request:

Code: 
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
 
I am trying to get this info for 5 days now and when I add
Code: 
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:
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
 
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.
 
Alan or anybody,

I have tried to modify the above successful code that prints to 7 day forcast sheet to do almost the same for 7daySMMP check. What I need is for a macro to work with (see the attached sheet) having the output basically the same but with the following criteria. If todays date and column A26 thru A47 has a date then nothing carried forward to 7daySMMPcheck sheet. If todays date and nothing entered into cells A26 thru 47 then I need the name in B26 thru 47 and the text from cell in row 25 that corresponds to the date due (same as 7dayforcast). I need it to do the same 7 days as the 7day forcast does. I will make another button and place it on the SMMP tracking sheet to print this info on demand the same as 7day forcast.

Thanks for any help you can provide.
 

Attachments

  • Copy of SMMP tracking sheetemty.xlsm
    55.4 KB · Views: 1
You must log in or register to reply here.
Back
Top