Closed Thread
Results 1 to 7 of 7

VBA - Compare the data across different sheets and put into one sheet

  1. #1
    Registered User
    Join Date
    07-18-2011
    Location
    Hong Kong
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    31

    VBA - Compare the data across different sheets and put into one sheet

    Dear all,

    I get 1 task of changing the code written by predecessor to comparing the data from several sheets.

    1. In the sheets it use several times, I found the original very complicated, can anyone suggest to simplify the code?

    2. When execute the procedures, I found some errors, i think it may be due to predecessor partly use macro recording.

    Detail of how is the logic can be seen from the file.

    Thanks in advance!

    kw42chan
    Long idle 3.5 P-drive.xls

    longidle.xls

  2. #2
    Registered User
    Join Date
    07-18-2011
    Location
    Hong Kong
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    31

    Re: VBA - Compare the data across different sheets and put into one sheet

    Quote Originally Posted by kw42chan View Post
    Dear all,

    I get 1 task of changing the code written by predecessor to comparing the data from several sheets.

    1. In the sheets it use several times, I found the original very complicated, can anyone suggest to simplify the code?

    2. When execute the procedures, I found some errors, i think it may be due to predecessor partly use macro recording.

    Detail of how is the logic can be seen from the file.

    Thanks in advance!

    kw42chan
    Attachment 126613

    Attachment 126614
    one more file "ci" provided
    ci.xls

  3. #3
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: VBA - Compare the data across different sheets and put into one sheet

    I have gone through all 3 attachments. It will be very time consuming to find out what the code is trying to do. Its better you explain to us what task you need automated step by step, and we can try and provide you a fresh code.

  4. #4
    Registered User
    Join Date
    07-18-2011
    Location
    Hong Kong
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    31

    Re: VBA - Compare the data across different sheets and put into one sheet

    Quote Originally Posted by arlu1201 View Post
    I have gone through all 3 attachments. It will be very time consuming to find out what the code is trying to do. Its better you explain to us what task you need automated step by step, and we can try and provide you a fresh code.
    Thanks Arlu1201!

    As I found the whole code is different to reexamine, I revise the documents into one with more concrete requirement, hope that everyone can easily understand.

    Thanks again!
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    07-18-2011
    Location
    Hong Kong
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    31

    VBA code for Vlookup the value then return the date

    [/QUOTE]Dear all,

    I have 1 file with 2 worksheets, MR and MR (ytd). I want to write 1 procedure for a report to lookup the container no at MR from MR (ytd), then return the date.

    Now I just use the formula vlookup to do this function.

    =IF(ISERROR(VLOOKUP($B5,'MR (ytd)'!$B$5:$O$805,14,0)),"0",VLOOKUP($B5,'MR (ytd)'!$B$5:$O$805,14,0)+$B$2-'MR (ytd)'!$B$2)


    I have write up some code for it. But find it is cannot lookup.

    [Code found here]

    Sub mrdays()
    Dim mrrecords As String
    Dim i As Integer
    Dim to_date As Date
    Dim lsrpdate As Date

    With Sheets("MR")
    mrrecords = .Cells(Rows.Count, 1).End(xlUp).Row
    to_date = .Cells(2, 2).value
    End With

    With Sheets("MR (ytd)")
    lsrpdate = .Cells(2, 2).value
    End With

    For i = 0 To mrrecords

    For Each C In Worksheets("MR").Range("B5:B700").value '.SpecialCells(xlCellTypeConstants, 2)

    With Worksheets("MR (ytd)")
    On Error Resume Next
    Set x = .Range("B5:B700").Find(C.value, LookIn:=xlValues, lookat:=xlWhole)
    End With

    i = i + 1

    With Worksheets("MR").Range("O5")

    If x Is Nothing Then
    .Offset(i, 0).value = Worksheets("MR").Range("O5").value + DateDiff("d", lsrpdate, to_date)
    Else
    .Offset(i, 0).value = 0
    End If

    End With
    Next C

    Next

    End Sub
    Attached Files Attached Files
    Last edited by kw42chan; 10-31-2011 at 01:45 AM.

  6. #6
    Forum Expert ConneXionLost's Avatar
    Join Date
    03-11-2009
    Location
    Victoria, Canada
    MS-Off Ver
    2010
    Posts
    2,952

    Re: VBA - Compare the data across different sheets and put into one sheet

    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here
    Would you like to say thanks? Please click the: " Add Reputation" button, on the grey bar below the post.

  7. #7
    Forum Expert ConneXionLost's Avatar
    Join Date
    03-11-2009
    Location
    Victoria, Canada
    MS-Off Ver
    2010
    Posts
    2,952

    Re: VBA - Compare the data across different sheets and put into one sheet

    This is a duplicate post and as such does not comply with Rule 5 of our forum rules. This thread will now be closed, you may continue in your other thread.

    Thread Closed.

Closed Thread

Thread Information

Users Browsing this Thread

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

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