Using Macro To Create Financial Statements From Journal Entries
Jan 8, 2014
I'm completely new with macros. I was wondering what you would need so that the information will automatically update itself onto a summary page. For example for entries on one worksheet to create a statement on another worksheet?
Example:
Asset Type
Description
Date
Amount
OA
[Code]....
The first entry doesn't have a date, because the asset type is classified as OA. (therefore an exception) Each of this information will be linked to it's own worksheet. For example, the OA entries would create their own sheet which will summed.
1) When using the command below, how can I determine what the "XYZ" component could be. I see plenty of examples of it being used to start a MS Word session or Powerpoint but I'd really like to see a definitive list.
At the moment, I'm not even sure what to type into Google that describes this part of the command's argument.
Set target = CreateObject("XYZ.Application")
2) I'm just a frightened noob on this site and I tried to follow the advice on bumping my previous post on this subject, having altered my plea. All I got was a telling-off for being a double-poster and my old old thread sat where it was despite my 'Bump' command. Do you have to have at least one reply before your thread moves?
Transaction #Transaction DescriptionAccountDate (XX/XX/XX)DesciptionABCDEFG10/24/2015Cash Held(3404.00)3404.00 0.00 0.00 50.00 0.00 (50.00)10/25/20152Cash not Held0.00 (9707.00)9707.00 0.00 (60.00)60.00 0.00 10/26/2015Cash Held0.00 (7402.00)0.00 7402.00 0.00 0.00 0.00 10/27/20154Transfer to Bank 0.04 0.00 (50.04)0.00 50.00 0.00 0.00 10/28/20155Transfer of Cash0.00 4208.00 0.00 (4208.00)0.00 0.00 0.00
I am trying to make a macro that will turn transpose the above data into the below data.
DateDescriptionAccountAmount10/24/2015Cash HeldA-340410/24/2015Cash HeldB340410/24/2015Cash HeldE5010/24/2015Cash HeldG-5010/25/2015#2 Cash not HeldB-970710/25/2015#2 Cash not HeldC970710/25/2015#2 Cash not HeldE-6010/25/2015#2 Cash not HeldF6010/26/2015Cash HeldE-740210/26/2015Cash HeldG7402
Basically each non-zero number in the accounts (A through G) need to have their own row with the account name from their appropriate column and the date and description in their appropriate row. So as you can see each account A,B,E,and G get the amount -3404, 3404, 50, and -50 in their own row and get the appropriate descipion of 'Cash Held' and Date '10/24/2015'. Then the macro would move onto the next row and do the same thing.
Also if there is a way to tack in front of the description the transaction # (if there is one) ie. #2 Cash not Held. I really don't even know where to start.
I use online banking for paying my monthly bills. I get different confirmartion for each pay transection. Pls look attached Excel file. Now I want to find specific bills expense with amount from all my 12 months bank statements. How can I get this task done.
Note: Each time get different confirmation which appears on my bank statement beside paid bill name. for example January 2009 statement GAS Z8A.... Feb 2009 GAS S2W. I hope this clarify. What I have to do if I want to get all my 12 months GAS money withdraw from bank statement. I use csv file for bank statement.
I'm researching a load of financial data downloaded for me from Bloomberg. It lists daily prices of 15 commodities for nearly 20 years in some cases! Its far too much data to get anything worthwhile out of, so I'd like to know if any of you guys have used or know what the VBA is to write a macro that will reduce all the data from daily to monthly. (ie. I want a macro that will allow me to quickly delete all data corresponding to dates which are NOT the end of the month).
=IF(AND(ISBLANK(A3),NOT(ISNUMBER(I3))),"",IF(TODAY()-A3>=30,G3*0.5%,"$0.00")) How can I keep H from working if there is a negative balance in G? This will create negative interest.
i've got a bunch of vlookup statements in my worksheet and i now need to create if statements based on them. however, the if statements do not recognize the vlookuped values. how can i get the if statements to work based on what the cell SHOWS and not what the formula is? i realize i can copy and paste values but i would like to retain this worksheet as a template.
I am trying to create a drop down list from a named range that has duplicate names listed. I cannot delete any of the duplicate rows. Is there an easy way to create the list with each name represented only once?
I am using excel 2003 to count my inventory in the following manor:
I have a "database sheet" that is comprised of possible inventory SKU numbers.
I have a "scanned sheet" that lists all the SKU's that I have scanned in the warehouse
I have a "count sheet" that counts the number of times each SKU in the database appears in the "scanned sheet"
Am I going about this in the most efficient manor?
Question 2: Frequently, new Items appear in the warehouse that are not yet in my database. Is it possible to create a list of SKU numbers that do not have a match in the database? As it stands, any SKU numbers that are scanned but not already listed in my database - simply do not get counted.
I have an excel sheet i would like to make into a journal. I would like to make a page for each day of the year. I want to duplex print it, but i cant seem to do it directly from excel would it be possible just to make the file into a pdf?
Here is the macro i was trying:
Sub ghgh() Dim i As Long, temp As String temp = InputBox("Enter date") If Not IsDate(temp) Then Exit Sub For i = 0 To 2
[Code] ....
Trouble is it only does one page at a time, so i cant duplex print.
I am trying to create this macro for my accounting journal What I want to happen is that in my sheet1 if the 1st cell in column a is "CASH" then the whole row should be copied and pasted in sheet 2. i want this to happen from the first cell in a column until the very last data in column a which means i am not certain up to what row number it will have data since this is a journal with uncertain number of transactions.
I've been trying to code this user form so that when the appropriate command button is pushed, the Inventory Number is populated the amount of times indicated by the "multiplier" number.
I haven't gotten very far successfully. Mostly runtime errors.
Here is a screen shot of my table, along with my non working code.
Private Sub cmdfront_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("Scanned")
I have a spreadsheet with 4 columns of information, then a column for a primary name, and then many columns of secondary names (with a different number of secondary names for each row).
For rows with more than 1 secondary name, I need to create a new row with a copy of all the columns up to the secondary name for each secondary name in the row.
A row in the spreadsheet looks like this:
data data data data PrimaryName SecondaryName1 SecondaryName2 SecondaryName3... SecondaryNameN
I need this row to be replaced by the following set of rows:
data data data data PrimaryName SecondaryName1
data data data data PrimaryName SecondaryName2
data data data data PrimaryName SecondaryName3 ... data data data data PrimaryName SecondaryNameN
I am trying to create a excel file that takes information off the website (AAPL Annual Income Statement - Apple Inc. Annual Financials) based on a stock name I enter in the first excel sheet. So when I type in the stock name, and click on the Search button (runs the VBA program) i created, it will search on the website for that stock and retrieve the 5 years financial data. How can I incorporate web query function in it?
Sub AddNewWorksheet() Dim Val As String Val = Sheets(1).Range("B4").Value Sheets.Add ActiveSheet.Name = Val ActiveSheet.Move after:=Sheets(4) End Sub
In that file, you can see there is a list of somewhat similar data across 5 columns. Some of the entries are duplicates, some are different by only one character. What I want to be able to do is generate a list of all the unique rows of data, taking into account the data from ALL 5 columns. The list can be on another sheet or simply just further down on the same sheet, that doesn't matter too much.
I could do it if it were just one column of data, but struggling trying to do it with several columns.
I have a large table of data covering 3 different companies and need to create a second table of data counting how many entries there are for each month in each company. This second table is to be used to create bar graphs and I am not using pivot tables as I cannot work out how to get the pivot table to insert months where nothing has happened. The attachment should make it clear
I have three IF statements as below. the problem is if the first statement is true I want it to skip the next two statements or the result will be changed again.
DATE A B C D E 2/22/2008TRUEFALSEFALSEFALSERon 2/23/2008FALSETRUETRUETRUEPhill 2/24/2008FALSETRUEFALSEFALSETracy 2/25/2008FALSEFALSEFALSEFALSESharon 2/26/2008TRUETRUEFALSETRUEBill
On sheet two I need to list any date that has three or more true statements with the coresponding name.
I've done a few searches and found the 2 macros I need. They are:
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 4 And Target.Count = 1 Then If InStr(1, Target.Value, "REL", vbTextCompare) Then If IsEmpty(Target.Offset(0, -1)) Then Target.Offset(0, -1).Value = Date Target.Offset(0, 1).NumberFormat = "mm/dd/yyyy" Target.EntireColumn.AutoFit End If End If End If End Sub
and Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Target If .Count > 1 Then Exit Sub...........................
I know very little about VBA. The first program I use to enter a static date to the cell (Column C) to the left when I type "REL" into a cell (Column D) and the second will be used to enter a static date into the cell next to the one above (Column B) when I type anything into a cell (Column E).
They both work individually but I am now trying to compile them together and get the compile error (Because they have the same worksheet_change name). I do not know how to rename or make them in the same.
I created the driver macro Mike2 (At first cosisting only of the first 2 lines) with keyboard shortkey +m, to run macro Mike. (In future additionals macros). Every time I run this macro it adds 2 lines to the Macro ,regardless of the End and Exit sub statements. Why?? Do not want this to happen.
I'm needing a macro that will allow me to get around the limits of no more than 7 IF statements and using a SUMPRODUCT formula as well. I need the total or sum of the macro/formula to be in cell "DB8".
Here's my formula: =SUMPRODUCT(IF(CP8=M11,EXACT(K7,"DI"),0)+0+SUMPRODUCT(IF(CP8=W11,EXACT(U7,"DI"),0)+0+SUMPRODUCT(IF(CP8=AG11,EXACT(AE7,"DI"),0)+0+SUMPRODUCT(IF(CP8=AQ11,EXACT(AO7,"DI"),0)+0+SUMPRODUCT(IF(CP8=BA11,EXACT(AY7,"DI"),0)+0+SUMPRODUCT(IF(CP8=BK11,EXACT(BI7,"DI"),0)+0+SUMPRODUCT(IF(CP8=BU11,EXACT(BS7,"DI"),0)+0+SUMPRODUCT(IF(CP8=CE11,EXACT(CC7,"DI"),0)+0+SUMPRODUCT(IF(CP8=CO11,EXACT(CM7,"DI")+0,0))))))))))
Financial Year ended on 31st March. If date is 18th August 2008, Then last date for financial year is 31st March 2009. IF date is 15th March 2009, Then also last date for financial year is 31st March 2009. I want formula / function to get last date of financial yera.
I have a report at the moment which shows me the calendar year that an action was carried out in, what I would also like to show is which of our financial years (they run April to March) the action was carried out in.
I have created a monster formula that extracts the Australian financial year (FYR) from a date string and would like to convert the formula to a nice tidy function.
the formula works on a date string with the following format: yyyy-mm-dd (2008-12-15) for example the formula result for 2008-12-15 is 08/09 (yy/yy)
=IF(OR(LEFT(ExDate,1)+13,MID(ExDate,5,1)"-",MID(ExDate,8,1)"-",LEN(ExDate)10),"Change the expiry date format to yyyy-mm-dd", IF(OR(MID(ExDate,3,1)+1=1,MID(ExDate,3,2)+1=11), IF((MID(ExDate,6,2)+1)
