Excel VBA to extract data based on various criteria (cell values)

We have been manually checking and reconciling our client's bank statements by hand every day. Now we would like to automate it as much as possible. So instead of looking up the prior day's data from a pdf and enter it into a cell, the bank provides statements in xls as well. However, really ugly xls file, that are more or less just the pdf in xls, but not a nice table structure.

So I am looking for a solution that would search for example from row 1 downwards for the account number 0038797031 in column K. after this row, it needs to search for the string "CLOSING" in column C. if that is found, the value on that row in column O should be returned or fetched to another sheet (or for the purpose of this answer just displayed as message box).

Then the code would continue downwards and look for account number 0078943421 in column K. and again if found, continue the rows downwards in column C for "CLOSING", if found cell value in column O of that row should be returned.

Many thanks for your help!
BrdgBldrAsked:
Who is Participating?
 
Juan OcasioApplication DeveloperCommented:
Here is the code:
Sub FetchAccountBalances()

Dim SourceWb, TargetWb As Workbook
Dim SourceWs, TargetWs As Worksheet
Dim LastRow As Integer
Dim Closing As String
Dim AccountNo, AccountNo1, AccountNo2 As String

    Set TargetWb = ActiveWorkbook
    Set SourceWb = Workbooks("EE-Test-Balances.xls")
    Set SourceWs = SourceWb.Sheets("Sheet1")
    
    
    
SourceWs.Activate

LastRow = ActiveSheet.UsedRange.Rows.Count
AccountNo1 = "1238193921"
AccountNo2 = "3414314314"
Closing = "CLOSING"
 

For i = 1 To LastRow
    If CStr(Cells(i, 11).Value) = AccountNo1 Or CStr(Cells(i, 11).Value) = AccountNo2 Then
        AccountNoRow = i
        AccountNo = CStr(Cells(i, 11).Value)
        For x = AccountNoRow To ActiveSheet.UsedRange.Rows.Count
        If Cells(x, 3).Value = Closing Then
            MsgBox "Account " & AccountNo & " has a balance of " & Cells(x, 15).Value
            i = x
            Exit For
        End If
        Next x
    End If
Next i
    



End Sub

Open in new window


0
 
Juan OcasioApplication DeveloperCommented:
can you provide an example?
0
 
SiddharthRoutCommented:
Here is a sample for what you want. Please amend it for realistic situation. :)

Sub Sample()
    Dim oSht As Worksheet
    Dim StartRow As Long, lastRow As Long
    Dim strSearch As String
    Dim aCell As Range, bCell As Range
    Dim KRange As Range, CRange As Range
    
    On Error GoTo Err
    
    Set oSht = Sheets("Sheet1")
    
    lastRow = oSht.Range("K" & Rows.Count).End(xlUp).Row
    
    Set KRange = oSht.Range("K1:K" & lastRow)
    
    '<~~ Account number
    strSearch = "0038797031"
    
    Set aCell = KRange.Find(What:=strSearch, LookIn:=xlValues, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)
    
    '<~~ If Account number Found
    If Not aCell Is Nothing Then
        StartRow = aCell.Row
        lastRow = oSht.Range("C" & Rows.Count).End(xlUp).Row
        
        If lastRow < StartRow Then Exit Sub
        
        Set CRange = oSht.Range("C" & StartRow & ":C" & lastRow)
        
        strSearch = "CLOSING"
        
        Set bCell = CRange.Find(What:=strSearch, LookIn:=xlValues, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)
        
        If Not bCell Is Nothing Then
            '~~> This will give you the value from Col O in the relevant row
            MsgBox oSht.Range("O" & bCell.Row).Value
        End If
    End If
    Exit Sub
Err:
    MsgBox Err.Description
End Sub

Open in new window


Sid
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
Saqib Husain, SyedEngineerCommented:
One point comes to my mind

>>>...to search for the string "CLOSING" in column C. if that is found, .....

what if not found?
0
 
gtglonerCommented:
Here is an example, let me know what you think:
Book1.xls
0
 
BrdgBldrAuthor Commented:
okay... seems my explanation was not as professional as the solutions one gets here... neither Sid's nor gtgloner's code works, probably because I was not accurate enough with my explanations...

I upload here an example sheet. the columns of the source worksheet have often emtpy fields or space inbetween...

the account number fields and the balance to get are coloured... EE-Test-Balances.xls
0
 
BrdgBldrAuthor Commented:
I was trying to go this way as approach (probably not very professional)... but I don't get there yet, it returns the correct row for the account number, but not for the row with the next following "BALANCE" string after the account number...

Sub FetchAccountBalances()

Dim SourceWb, TargetWb As Workbook
Dim SourceWs, TargetWs As Worksheet
Dim LastRow, AccountNo, AccountNoRow, BalanceRow As Long
Dim Closing As String

    Set TargetWb = ActiveWorkbook
    Set TargetWs = TargetWb.Sheets("CashReconciliation")
    Set SourceWb = Workbooks("EE_Test_Balances.xls")
    Set SourceWs = SourceWb.Sheets("Sheet1")
    
SourceWs.Activate

LastRow = SourceWs.Range("K" & Rows.Count).End(xlUp).Row
AccountNo = "1238193921"
Closing = "CLOSING"

For i = 1 To LastRow
    If SourceWs.Range("K" & i).Value = AccountNo Then AccountNoRow = i
Next i
    
For b = AccountNoRow To LastRow
    If SourceWs.Range("C" & b).Value = Closing Then BalanceRow = b
Next b

MsgBox ("Account No. " & AccountNo & "balance is on row " & BalanceRow)


End Sub

Open in new window

0
 
Juan OcasioApplication DeveloperCommented:
Try this:


Sub FetchAccountBalances()

Dim SourceWb, TargetWb As Workbook
Dim SourceWs, TargetWs As Worksheet
Dim LastRow, AccountNo, AccountNoRow, BalanceRow As Long
Dim Closing As String
Dim Accountnum As Long

    Set TargetWb = ActiveWorkbook
    'Set TargetWs = TargetWb.Sheets("CashReconciliation")
    Set SourceWb = Workbooks("EE-Test-Balances.xls")
    Set SourceWs = SourceWb.Sheets("Sheet1")
    
    
    
SourceWs.Activate

LastRow = ActiveSheet.UsedRange.Rows.Count
AccountNo = "1238193921"
Closing = "CLOSING"

For i = 1 To LastRow
    If CStr(Cells(i, 11).Value) = AccountNo Then
        AccountNoRow = i
        For x = AccountNoRow To ActiveSheet.UsedRange.Rows.Count
        If Cells(x, 3).Value = Closing Then
            MsgBox Cells(x, 15).Value
            i = x
            Exit For
        End If
        Next x
    End If
Next i
    
'For b = AccountNoRow To LastRow
'    If SourceWs.Range("C" & b).Value = Closing Then BalanceRow = b
'Next b

'MsgBox ("Account No. " & AccountNo & "balance is on row " & BalanceRow)


End Sub

Open in new window

0
 
BrdgBldrAuthor Commented:
@jocasio123: this works perfect and returns the correct account balance!

last small question: how can I enter the various account numbers that must be looked up and have your subroutine

For i = 1 To LastRow
    If CStr(Cells(i, 11).Value) = AccountNo Then
        AccountNoRow = i
        For x = AccountNoRow To ActiveSheet.UsedRange.Rows.Count
        If Cells(x, 3).Value = Closing Then
            MsgBox Cells(x, 15).Value
            i = x
            Exit For
        End If
        Next x
    End If
Next i

Open in new window


being run n times for all the account numbers?

many, many thanks!

0
 
Juan OcasioApplication DeveloperCommented:
You can create a front page with a textbox and a submit button.  Then you can add then number in for each account and press submit.  What you may want to do is create a macro that will give you an open dialog box which will allow you to open the file that is downloaded, then asks you for the account number.  The other thing you could do is have it open the file and just reconcile the accounts one by one and give you a summary page of the account numbers and balances.

HTH
0
 
BrdgBldrAuthor Commented:
Thanks. The account numbers are fixed, a set of then numbers. I was thinking of a way that I had only to have your subroutine once, but called with the different variables, something like:

AccountNo1 = 12233456
AccountNo2 = 4242949240
AccountNo3 = 49249234

And then something for a = 1 to 3
        AccountNo = AccountNo & a
...

but I don't know how to do it...

Increased to 500 points...
0
 
Juan OcasioApplication DeveloperCommented:
As you can see, I've added a couple other variables for the account numbers AccountNo1, AccountNo2...  What you would do is Dim new variables based on the number of accounts, then set their values to the account numbers.

Then modify this line:

If CStr(Cells(i, 11).Value) = AccountNo1 Or CStr(Cells(i, 11).Value) = AccountNo2 Then
 
with more Or statements as in

If CStr(Cells(i, 11).Value) = AccountNo1 Or CStr(Cells(i, 11).Value) = AccountNo2 Or CStr(Cells(i, 11).Value) = AccountNo3 Then

It would be slicker to set it all up in an array, but this will also work nicely.

HTH!
0
 
BrdgBldrAuthor Commented:
thanks a lot to everybody for the help!
0
 
BrdgBldrAuthor Commented:
sorry jocasio123...

I would like to try it another way, as there might be a large number of accounts involved, so I tried this approach...

Set TargetRange1 = TargetWs.Range("ClientUsd1")
Set TargetRange2 = TargetWs.Range("ClientUsd2")
Set TargetRange3 = TargetWs.Range("ClientUsd3")
Set TargetRange4 = TargetWs.Range("ClientUsd4")
Set TargetRange5 = TargetWs.Range("ClientUsd5")
Set TargetRange6 = TargetWs.Range("ClientUsd6")
Closing = "CLOSING"

For a = 1 To 6
    For i = 1 To LastRow
        If CStr(Cells(i, 11).Value) = AccountNo & a Then
            AccountNoRow = i
            AccountNo = CStr(Cells(i, 11).Value)
            For x = AccountNoRow To ActiveSheet.UsedRange.Rows.Count
            If Cells(x, 3).Value = Closing Then
                AccountBalance = Cells(x, 15).Value
                i = x
            Exit For
            End If
            Next x
        End If
    Next i
TargetRange & a = AccountBalance
Next a

Open in new window


however, my approach doesn't seem to work... Is there a way to define a variable name based on the value of another variable (what I tried here with TargetRange & a)? If yes, how? Or otherwise only with arrays? (I intend to do the same with AccountNo1, AccountNo2 so as AccountNo & a)

Thanks for any help and input!
0
 
Juan OcasioApplication DeveloperCommented:
I'm not too sure what the targetrange is for.  Can you provide and example file?  I'm am assuming the ranges have the account numbers, but I'd like to see an example so I can help you further.
0
 
BrdgBldrAuthor Commented:
The idea is that in the target sheet, the account balance of AccountNo1 is written to TargetRange1, the account balance of AccountNo2 to TargetRange2, etc.
0
 
Juan OcasioApplication DeveloperCommented:
Please send an example of your existing file so I can look at it.  That way, I'll be able to create something that'll work for you.  Are you creating the target sheet with the range of accounts everytime or is it already developed?  Where is this sheet located?
0
 
BrdgBldrAuthor Commented:
@jocasio123 I will post examples and questions as separate, related question with new points.

:o)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.