Results 1 to 14 of 14

How to match Date and Amount from Sheet1 to Sheet2 with the same Date and Amount?

Threaded View

  1. #1
    Registered User
    Join Date
    04-05-2013
    Location
    Philippines
    MS-Off Ver
    Excel 2007
    Posts
    13

    Unhappy How to match Date and Amount from Sheet1 to Sheet2 with the same Date and Amount?

    How to write macro that would match the first sheet's date with the other sheet's date then counter checking the amount (if date and amount are the same) then it would copy the whole data from the 2nd sheet beside the first sheet data (that had matched)

    I wrote something that goes like this:

    Sub MatchFundTransferData()
        Dim Found As Range, FirstFound As String, rngDate As Range, counter As Long
        Dim ws1 As Worksheet, ws2 As Worksheet
        Set ws1 = Sheets("Sheet1")  'Destination worksheet
        Set ws2 = Sheets("Sheet2")  'Source worksheet
        Application.ScreenUpdating = False
        For Each rngDate In ws1.Range("A:A").SpecialCells(xlCellTypeConstants, xlNumbers)
                If IsEmpty(rngDate.Offset(, 5)) Then
                Set Found = ws2.Range("A:A").Find(What:="Date" & rngDate.Value, _
                                                  LookIn:=xlValues, _
                                                  LookAt:=xlWhole, _
                                                  SearchOrder:=xlByRows, _
                                                  SearchDirection:=xlNext, _
                                                  MatchCase:=False)
                If Not Found Is Nothing Then
                    FirstFound = Found.Address
                    Do
                        If Found.Offset(, 5).Value = rngDate.Offset(, 1).Value Then
                            Found.Offset(, -2).Resize(, 8).Cut Destination:=rngDate.Offset(, 5)
                            counter = counter + 1
                            Exit Do
                        End If
                        Set Found = ws2.Range("A:A").FindNext(After:=Found)
                    Loop Until Found.Address = FirstFound
                End If
            End If
        Next rngDate
        Application.ScreenUpdating = True
        MsgBox counter & " Data Had Matched", vbInformation, "Matching Check Data Complete"
    End Sub
    but i think it's not working..
    Attached Files Attached Files
    Last edited by ims0phie; 04-21-2013 at 08:57 AM.

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