Excel Busn Math 34: Bank Checks

Hello,
I'm trying to figure out the best way to tie what I have to what the bank has for end of month reconciliation. In the excel file below, the first three tabs are the data that I have... what checks I issued out, what checks have cleared my system, and what the bank has that cleared, is outstanding, or was voided.

I'm trying to figure out the best way to tie these out using something on the 4th tab that pulls info from the other three tabs. If anyone has any ideas, please let me know.

Thanks,
Tony

from my general ledger I can get an excel file of all checks issued during a time period. unfortunately our system does not automatically delete voided checks but rather it reenters the check number with a corresponding negative amount

example of voided check
check # 114, $200
check # 114, -$200

These two entries will show up intermixed in large list of nonvoided and other voided checks.

I want to create a function where the user can push a cmd button which will then delete the voided checks.

I can think of one of two ways neither of which I know how to do. It could either find where check numbers match and delete those items, or produce an out put which is summed by check number, so #114 (in above example) = $0 and shows only those items with a balance > 0.

possible? any help or ideas very much appreciated. If it will help I can post the file tomorrow.

Jeremy

How to do a macro for Payment Voucher, Cash Book, and Bank Checks/Cheques?

I shall be much obliged if you guys could kindly help me using VBA codes or Macro to run the following.

1. I have worksheet named Payment Voucher in Book1.
1.1 Date in Cell G1
1.2 Name in Cell B4
1.3 Invoice amount in Cell B7
1.4 Amount in Number [1,450,000.38] in Cell F7

2. I hope that you guys could help me to do the following:-

2.1 When I key DATE in Cell G1 at Payment Voucher, DATE automatically appear in the following 2 worksheets.
2.1A) Cell A2 of worksheet Cash Book (Book2)
2.1B) Cell G1 of worksheet Bank Check (Book3)

2.2 When I key NAME in Cell B4 at Payment Voucher, NAME automatically appear in the following 2 worksheets.
2.2A) Cell C2 of worksheet Cash Book (Book2)
2.2B) Cell B4 of worksheet Bank Check (Book3)

2.3 When I key AMOUNT in Cell F7 at Payment Voucher, NUMBER automatically appear in the following 2 worksheets.
2.3.A) Cell E2 of worksheet Cash Book (Book2)
2.3.B) Cell F6 of worksheet Bank Check (Book3)
2.3.C) Cell B6 of worksheet Bank Check, Number in TEXT must appear like this: One Million Four Hundred Fifty Thousand And Cents Thirty Eight Only

Any suggestion and advice is highly welcomed and appreciated.

Thousand thanks in advance.

Susan

Hello all,

Please see below i inserted the cells what i want the excel counts,
I want that excel checks all the left cells, he recognize that text2 is in the cell, if yes then it takes the right next cell value and sums with the other where he also found "text2". So for final result in the below case should be ????=1400.

Waiting for your help.

text 100,00 text2 200,00 text2 200,00 text2 200,00 Text2 : ???? text2 200,00 text 100,00 text2 200,00 text 200,00 text2 200,00 text 100,00 text2 200,00

Dear All,
I require to reconcile 2 sheets every month and its very tedious.
Can this be automated by a macro. here is what I need to do.
sheet 1 is the bank statement , sheet 2 is the reconciliation sheet, where I have to search the items from the sheet 1 and if the item is present in sheet 2 then I have to enter the date from sheet 1 to sheet 2, thus clearing all the similar items in both the sheets.
Pl. help thanks

I am running a macro from excel that copies tables and charts into a PowerPoint template. If the template file is already open I'd like to set it as the presentation I'm going to work with and if it is not open I want to open it. I have tried to adapt a number of approaches that I found from other places but I could not get anything to work. Here are a few of the thing I tried:

Code:

Dim pptPrs As PowerPoint.Presentation
Dim pptApp As PowerPoint.Application
Dim opnFlg As Boolean

    opnFlg = False
    For Each pptPrs In PowerPoint.Presentations
        If pptPrs.Name = "template.pptx" Then
            opnFlg = True
            Set pptApp = GetObject(, objPrs)
            pptApp.Visible = True
        End If
    Next
    If opnFlg = False Then
        Set pptApp = New PowerPoint.Application
        pptApp.Visible = True
        pptApp.Presentations.Open Filename:="C:\test\template.pptx"
    End If

Here is a function someone wrote and I modified somewhat that only does the checking to see if the presentation is open... however this also I could not get to work:

Code:

Function IsPresentationOpen(strPresName As String) As Boolean
Dim oPresObject As PowerPoint.Presentation
Dim boolIsFullPath As Boolean

   ' Check to see whether the full path was passed.
   If (InStr(1, strPresName, ":\")) = 0 Then
      boolIsFullPath = False
   Else
      boolIsFullPath = True
   End If

   ' Loop through the open presentations.
   Set oPresObject = CreateObject("PowerPoint.Presentation")
   For Each oPresObject In PowerPoint.Presentations
      If boolIsFullPath = True Then
         ' Check for a match.
         If (StrComp(oPresObject.FullName, strPresName, vbTextCompare) = 0) Then
            IsPresentationOpen = True
            Exit Function
         End If
      Else
         ' Check for a match.
         If (StrComp(oPresObject.Name, strPresName, vbTextCompare) = 0) Then
            IsPresentationOpen = True
            Exit Function
         End If
      End If
   Next oPresObject

   ' No match found.
   IsPresentationOpen = False

End Function

Any help is much appreciated. Thanks!

Any body has any idea on how to Automate the Bank Reco in Excel.
I mean at the click of a button, we should get the diff. of the Bank statement and the Bank book in a seperate sheet.

Anybody ?

I would be highly obliged.

When I download my barclays statements into excel it does not download
properly. The data is not in column format.

I have bought some preprinted checks (voucher style) to run through my laser
printer and am looking for a simple template for word or excel that I can use
to ill out the check on my computer.

Hey guys,

I was wondering if this was possible to create a script where it can autodownload from bank websites to download monthly bank statements.
I have done macro for currency exchange rates, but not when it comes to actually downloading a pdf. Is this possible?

If not, is this even possible with regular VBA? What would I need to learn and know in order to get this done. I am guessing Microsoft SQL??

Thanks