Solved

Posted on 2011-05-04

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!

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!

18 Comments

```
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
```

Sid

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

what if not found?

Here is an example, let me know what you think:

Book1.xls

Book1.xls

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

```
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
```

```
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
```

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
```

being run n times for all the account numbers?

many, many thanks!

HTH

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...

```
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
```

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!

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
```

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!

Title | # Comments | Views | Activity |
---|---|---|---|

Excel spreadsheet only viewable in full screen mode | 5 | 37 | |

Detect file exist or not | 3 | 40 | |

Vlookup for IP | 3 | 33 | |

Excel 2010 - Custom Calculation in Pivot Table | 12 | 23 |

Join the community of 500,000 technology professionals and ask your questions.

Connect with top rated Experts

**17** Experts available now in Live!