Find Method in Macro

[tgwinford]

Hey guys, I'm working on something for my dad. He has a spreadsheet (Sheet 1) of checks that have been written by his company during the month (currently at 3000 checks/rows). The range includes check number, account number, amount, and status of the check (either 'C' for Cleared or 'W' for Withstanding).

Each day he gets a report from the bank that includes which checks cleared that day (Sheet 2). Some days it's 5 checks, other days it's 50, so the range changes each day. He wants to be able to run a macro that would search Sheet 1 for the the check number and amount for each of the checks in Sheet 2 and change the status to 'C'.

So, for example, he gets from the bank that 3 checks cleared today: Check 100 for $100, Check 104 for $50, and Check 104 for $200. (There are two Check 34s because there are multiple accounts, so sometimes the check numbers overlap. This is why he needs it to match the check number and the amount.)

How would the macro be written so that it searched Sheet 1 for Check 100 & $100 in Columns B and C and changed Column D to 'C'?

Thanks.

 

[dmt32]

Hi,
not sure if fully understood correctly but have a play with this & see if goes in right direction for you.
Place both codes in standard module & assign the FindCheck code to a button.

Sub FindCheck()
    Dim sPrompt As Variant
    Dim sTitle As String
    Dim InputVal(2) As Variant
    Dim Retry As Boolean


    Dim i As Integer
Top:
    sPrompt = Array("Check No.", "Check Amount")
    i = 0
    Do
        sTitle = sPrompt(i)
        InputVal(i) = InputBox("Enter " & sPrompt(i), sTitle)
        If StrPtr(InputVal(i)) = 0 Then
            Exit Sub
        ElseIf Not IsNumeric(InputVal(i)) Then
            MsgBox sPrompt(i) & Chr(10) & "Must Be A Number", 16, "Error"
        Else
            i = i + 1
        End If
    Loop Until i > 1
    UpdateCheck CLng(InputVal(0)), CCur(InputVal(1)), Retry
    If Retry Then GoTo Top
End Sub


Sub UpdateCheck(ByVal check As Long, ByVal Amount As Currency, ByRef Retry As Boolean)
    Dim wsReport As Worksheet, wsMaster As Worksheet
    Dim c As Range, c1 As Range
    Dim lastrow As Long


    Set wsReport = Worksheets("Sheet2")
    Set wsMaster = Worksheets("Sheet1")


    lastrow = wsReport.Range("B" & wsReport.Rows.Count).End(xlUp).Row


    'Report worksheet (sheet2)
    For Each c In wsReport.Range("B1:B" & lastrow)
        If c.Value >= check And c.Offset(0, 1).Value = Amount Then
            lastrow = wsMaster.Range("B" & wsMaster.Rows.Count).End(xlUp).Row
            'update master worksheet (sheet1)
            For Each c1 In wsMaster.Range("B1:B" & lastrow)
                If c1.Value >= check And c1.Offset(0, 1).Value = Amount Then
                    c1.Offset(0, 2).Value = "C"
                    MsgBox check & Chr(10) & "Record Updated", 48, "Updated"
                    Exit Sub
                End If
            Next c1
        End If
    Next c


    msg = MsgBox(check & Chr(10) & "Record Not Found" & Chr(10) & _
                 "Do You Want To Enter Another Check?", 36, "Not Found")
    If msg = 6 Then Retry = True


End Sub

When run, code prompts user to enter Check No & Amount in InputBoxes - searches sheet2 for a match & if found, updates sheet1.

Hope helpful

Dave