+ Reply to Thread
Results 1 to 3 of 3

How to match Date and Amount from sheet1 with sheet2 data with the same date and amount?

Hybrid View

ims0phie How to match Date and Amount... 04-28-2013, 12:39 AM
AlphaFrog Re: How to match Date and... 04-28-2013, 01:34 AM
ims0phie Re: How to match Date and... 04-28-2013, 02:23 AM
  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 with sheet2 data with the same date and amount?

    I don't know what's wrong with my script..
    it's not working.. or if it work.. it only copy the same date even the amount is different..
    it should be same date and same amount..
    and it should cut the data from sheet 2 so reconciling it would be much easier since i will only reconcile the items that are left from sheet 2..


    can anyone help me?

    Sub MatchOnlineFundTransfer()
        
        Application.ScreenUpdating = False
        
        Dim xlWs1 As Worksheet, xlWs2 As Worksheet
        Dim xlRngDate As Range
        Dim coll As New Collection
        Dim FirstFound As String
        Dim lngCount As Long, dCount As Long
        
        Set xlWs1 = ThisWorkbook.Worksheets("Sheet1")
        Set xlWs2 = ThisWorkbook.Worksheets("Sheet2")
        
        'Get Unique dates
        On Error Resume Next
        For Each xlRngDate In xlWs1.Columns(1).SpecialCells(xlCellTypeConstants, xlNumbers)
            If IsEmpty(xlRngDate.Offset(, 7)) Then
                coll.Add CDate(xlRngDate.Value), CStr(xlRngDate.Value)
            End If
        Next xlRngDate
        On Error Resume Next
    
        'Compare Date and Amount
        For lngCount = 1 To coll.Count
            Set Found = xlWs2.Range("C:C").Find(What:=CDate(coll(lngCount)), _
                                                  LookIn:=xlValues, _
                                                  lookat:=xlWhole, _
                                                  SearchOrder:=xlByRows, _
                                                  SearchDirection:=xlNext, _
                                                  MatchCase:=False)
            If Not xlRngDate Is Nothing Then
                If WorksheetFunction.CountIf(xlWs1.Columns(1), CDate(coll(lngCount))) = WorksheetFunction.CountIf(xlWs2.Columns(1), CDate(coll(lngCount))) Then
                    Set xlRngDate = xlWs1.Columns(1).Find(What:=CDate(coll(lngCount)), LookIn:=xlFormulas, lookat:=xlWhole)
                    counter = counter + 1
                    FirstFound = Found.Address
                    Do
                    If Found.Offset(, 5).Value = xlRngDate.Offset(, 1).Value Then
                        Found.Offset(, -2).Resize(, 8).Cut Destination:=xlRngDate.Offset(, 5)
                        xlRngDate.Offset(, 7).PasteSpecial xlPasteAll
                        counter = counter + 1
                        Exit Do
                        End If
                        Set Found = xlWs2.Range("H:H").FindNext(After:=Found)
                        Loop Until Found.Address = FirstFound
                    End If
                End If
        Next
        Application.ScreenUpdating = True
        MsgBox counter & " Data Had Matched", vbInformation, "Matching Check Data Complete"
        
    End Sub
    Attached Files Attached Files
    Last edited by ims0phie; 04-28-2013 at 01:34 AM.

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,651

    Re: How to match Date and Amount from sheet1 with sheet2 data with the same date and amoun

    Sub MatchOnlineFundTransfer()
        
        Application.ScreenUpdating = False
        
        Dim xlWs1 As Worksheet, xlWs2 As Worksheet
        Dim xlRngDate As Range
        Dim coll As New Collection
        Dim FirstFound As String
        Dim lngCount As Long, dCount As Long
        
        Set xlWs1 = ThisWorkbook.Worksheets("Sheet1")
        Set xlWs2 = ThisWorkbook.Worksheets("Sheet2")
        
        'Get Unique dates
        On Error Resume Next
        For Each xlRngDate In xlWs1.Columns(1).SpecialCells(xlCellTypeConstants, xlNumbers)
            If IsEmpty(xlRngDate.Offset(, 7)) Then
                coll.Add CDate(xlRngDate.Value), CStr(xlRngDate.Value)
            End If
        Next xlRngDate
        On Error GoTo 0
        
        'Compare Date and Amount
        For lngCount = 1 To coll.Count
            Set Found = xlWs2.Range("A:A").Find(What:=Format(coll(lngCount), "m/d/yy"), _
                                                  LookIn:=xlValues, _
                                                  lookat:=xlWhole, _
                                                  SearchOrder:=xlByRows, _
                                                  SearchDirection:=xlNext, _
                                                  MatchCase:=False)
            If Not Found Is Nothing Then
                If WorksheetFunction.CountIf(xlWs1.Columns(1), CDate(coll(lngCount))) = WorksheetFunction.CountIf(xlWs2.Columns(1), CDate(coll(lngCount))) Then
                    Set xlRngDate = xlWs1.Columns(1).Find(What:=CDate(coll(lngCount)), LookIn:=xlFormulas, lookat:=xlWhole)
                    counter = counter + 1
                    FirstFound = Found.Address
                    Do
                    If Found.Offset(, 5).Value = xlRngDate.Offset(, 1).Value Then
                        Found.Offset(, -2).Resize(, 8).Cut Destination:=xlRngDate.Offset(, 5)
                        xlRngDate.Offset(, 7).PasteSpecial xlPasteAll
                        counter = counter + 1
                        Exit Do
                        End If
                        Set Found = xlWs2.Range("A:A").FindNext(After:=Found)
                        Loop Until Found.Address = FirstFound
                    End If
                End If
        Next
        Application.ScreenUpdating = True
        MsgBox counter & " Data Had Matched", vbInformation, "Matching Check Data Complete"
        
    End Sub

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

    Re: How to match Date and Amount from sheet1 with sheet2 data with the same date and amoun

    umm.. i think something is still wrong.. nothing was copied from sheet 1.. though it detected 14 items..

+ Reply to 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