Create vba macro to extract balances from statement

Hi All,

I am new to VBA and need your help to create a macro that will help to extract deposit amount from different months for each account from a statement. There could be up to 12 months statement for each account and statement can have 1, 2 or up to 5 accounts. Please refer to the attachment for example statement.

Hope this will not be as big as it sounds to me.

Thank you in advance.

vkyjoshi

This has been solved by Excel Forum experts. here is the code, if someone runs in the similar problem:

Sub test()
Dim coll As New Collection, arr, i As Long, j As Long, strKey As String, v1, v2, total
With Sheets("Sheet1")
arr = .Range("B6:B" & Application.Max(.Cells(.Rows.Count, "B").End(xlUp).Row, 6))
For i = 1 To UBound(arr, 1)
If InStrB(1, arr(i, 1), "IMAGE:") Then
strKey = ""
For j = 1 To Len(arr(i + 2, 1))
If IsNumeric(Mid$(arr(i + 2, 1), j, 1)) Then strKey = Mid$(arr(i + 2, 1), j): Exit For
Next j
If Len(strKey) Then
On Error Resume Next
coll.Add Key:=strKey, Item:=New Collection
If Err.Number = 0 Then coll(strKey).Add "Account number " & strKey
On Error GoTo 0
End If
i = i + 2
End If
If InStrB(1, arr(i, 1), "Account summary") Then coll(strKey).Add Val(Replace$(arr(i + 2, 1), "All deposits in account $", ""))
Next i
ReDim arr(1 To 14, 1 To coll.Count)
j = 0
For Each v1 In coll
j = j + 1
i = 0
total = 0
For Each v2 In v1
i = i + 1
arr(i, j) = v2
total = total + Val(v2)
Next v2
arr(14, j) = total
Next v1
With .Range("D21").Resize(UBound(arr, 1), UBound(arr, 2))
.Value = arr
.NumberFormat = "$#,##0.00"
.Borders.Weight = xlThin
End With
End With
End Sub