Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Find and dates data across sheets

  1. #1
    New Member
    Join Date
    Oct 2014
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Find and dates data across sheets

    Hi all,

    i have a workbook which consists of 2 sheets. in each sheet i have a unique ID and the date next to it such as

    sheet 1

    98531 11/10/2014
    86521b 8/8/2014
    66363d 1/1/2014

    sheet 2
    86521b 10/8/2014
    98531 15/10/2014
    66363d 6/1/2014

    what i need to do is first find the data from sheet 2 which matches sheet 1. then minus sheet two date from sheet 1 to give me the amount of days difference.

    any help will be much appreciated

  2. #2
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    10,402
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Find and dates data across sheets

    Possibly try something like the code below (assumes that your id's are in column A and dates are in column B on both sheets)

    Code:
    Sub XXX()
        Dim c As Range, y As Long
    
        With Sheets("Sheet2")
    
            For Each c In .Range("A1:A" & .Range("A" & Rows.Count).End(xlUp).Row)
                y = Sheets("Sheet1").Columns("A:A").Find(What:=c.Value, After:=Sheets("Sheet1").Cells(1, 1), LookIn:=xlFormulas, _
                                                         LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext).Offset(, 1)
    
                c.Offset(, 2) = CLng(y) - CLng(c.Offset(, 1))
    
            Next
        End With
    
    End Sub
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  3. #3
    New Member
    Join Date
    Oct 2014
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find and dates data across sheets

    Hi

    Quote Originally Posted by MARK858 View Post
    y = Sheets("Sheet1").Columns("A:A").Find(What:=c.Value, After:=Sheets("Sheet1").Cells(1, 1), LookIn:=xlFormulas, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext).Offset(, 1)
    comes back with an error.

  4. #4
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    10,402
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Find and dates data across sheets

    "An error" doesn't mean anything to me. What does the error actually state exactly.
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  5. #5
    New Member
    Join Date
    Oct 2014
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find and dates data across sheets

    Hey Mark

    it would help if i put some data in there. worked a treat. thanks for the help mate

  6. #6
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    10,402
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Find and dates data across sheets

    it would help if i put some data in there
    Yep, might help
    Happy you got there.
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  7. #7
    New Member
    Join Date
    Oct 2014
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find and dates data across sheets

    hey

    i thought if i had the basic formula i would be able to apply it to sheet3 and sheet4, but i cant .

    so what i need done is the same thing but to sheet3 and sheet2 but output data to column d as sheet 1 outputs to column c. i hope that makes sense.

    a b c d
    8554c 11/10/14 7 data from sheet3 here
    8666d 12/6/14 6
    data from sheet3 here
    Last edited by Computerised10; Nov 11th, 2014 at 02:04 AM.

  8. #8
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    10,402
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Find and dates data across sheets

    OK, lets try and get this clear.
    Are you subtracting column B Sheet3 from column B Sheet2? and you are putting the output to what sheet in column D?

    Basically fill in the question marks below.

    subtracting Sheet? column? from Sheet? column?, output to Sheet? column?
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  9. #9
    New Member
    Join Date
    Oct 2014
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find and dates data across sheets

    ok its a sequence.

    first i subtract columnB Sheet 2 from columnB Sheet 1 out put to columnC sheet 1
    second i subtract ColumnB sheet 3 from columnb sheet 2 out put to columnD sheet 1
    third i subtract columnB Sheet 4 from CoumnB sheet3 out put to columnE sheet 1

  10. #10
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    10,402
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Find and dates data across sheets

    Untested...

    Code:
    Sub xxx2()
        Dim c As Range, y As Long, x As Long
    
        With Sheets("Sheet1")
    
            For Each c In .Range("A1:A" & .Range("A" & Rows.Count).End(xlUp).Row)
                y = Sheets("Sheet2").Columns("A:A").Find(What:=c.Value, After:=Sheets("Sheet2").Cells(1, 1), LookIn:=xlFormulas, _
                                                         LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext).Offset(, 1)
    
                c.Offset(, 2) = CLng(y) - CLng(c.Offset(, 1))
    
                y = Sheets("Sheet3").Columns("A:A").Find(What:=c.Value, After:=Sheets("Sheet3").Cells(1, 1), LookIn:=xlFormulas, _
                                                         LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext).Offset(, 1)
    
                x = Sheets("Sheet2").Columns("A:A").Find(What:=c.Value, After:=Sheets("Sheet2").Cells(1, 1), LookIn:=xlFormulas, _
                                                         LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext).Offset(, 1)
                c.Offset(, 3) = CLng(x) - CLng(y)
    
                y = Sheets("Sheet4").Columns("A:A").Find(What:=c.Value, After:=Sheets("Sheet4").Cells(1, 1), LookIn:=xlFormulas, _
                                                         LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext).Offset(, 1)
    
                x = Sheets("Sheet3").Columns("A:A").Find(What:=c.Value, After:=Sheets("Sheet3").Cells(1, 1), LookIn:=xlFormulas, _
                                                         LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext).Offset(, 1)
                c.Offset(, 4) = CLng(x) - CLng(y)
    
            Next
    
        End With
    
    End Sub
    Last edited by MARK858; Nov 11th, 2014 at 02:44 AM.
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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