Macro To Format Raw Data From Accounting System

Jun 14, 2013

I export raw data from my accounting system each month that I then format for use in another application that uses the data to produce customer statements. I have attached a file that shows the raw data in the first sheet and the formatted data in the second sheet. I recorded a macro while I did the process but I need to change the code to deal with dynamic data as the number of rows may be different every month.

Here are the steps I go through:
1. Clear the first 3 rows and the last 5 rows
2. Copy the totals in the last row of the data and paste them in the first row
3. Subtotal the different categories in row 2 and add a validity check
4. Add a new column A with a formula to add customer numbers on each line
=IF(ISERROR(FIND("00000",B5)),A4,B5
5. Copy the formulas and paste values over them
6. Filter the data to show blanks under the "Doc Date" column, delete all rows
7. Filter the data to show blanks under the "Type" column, delete all rows
8. Filter the data to show "Total:" under the "Apply No" column, delete all rows
9. Turn off filters, format all numbers in accounting format
10. Check the validation at the top to ensure no transactions were deleted

View 6 Replies


ADVERTISEMENT

$ Not Showing Up In Accounting Format

Jun 26, 2014

I have formatted several cells with an Accounting style. However, the dollar sign doesn't show up until I click in the command line.

View 5 Replies View Related

Format All Numbers With The Accounting Format

Jan 10, 2007

When working in Excel I format all numbers with the accounting format. I often use the single and double underlining feature on the Font tab of the Format Cells dialog box. Sometimes when I use the double underline it only puts (and prints) one underline. I've worked with the formatting many times with little success. The only way I can get the double underline to show up in this situation is to change the vertical cell alignment to be centered and then increase the cell height. This causes me other formatting heartaches. It's like the double underline is there it just will not show.

View 9 Replies View Related

VBA Find Zero Value (Dash) In Accounting Format

Aug 15, 2013

I have a column of data that may or may not have a formula in them. The cells are formatted in Accounting, w/o the leading $ sign. (i.e. 133.57) The value zero shows up as a hyphen or dash.

The issue is the format of the cell. In that attached worksheet, i inserted two formulas in cell b6 and b7. Both formulas are exactly the same, but the formatting is different. One is in the accounting format, but the other is in general. When I run the code, only the general formatted cell gets found.

My real worksheet is in the accounting formatted code, so I don't want to change my worksheet's numbers into a general format. How I can find the zero value thats in an accounting format?

Below is a strip down version of the code.

VB:
Sub test()[COLOR=#333333]
[/COLOR] Dim rLookInADR As Range
Dim foundcell As Range
Set rLookInADR = Range("b1:b380")
Set foundcell = rLookInADR.Find(what:=0, LookIn:=xlValues, lookat:=xlWhole)
MsgBox (foundcell.Row)
End Sub
[COLOR=#333333][/COLOR]

View 5 Replies View Related

Using The Accounting Format With No Decimal Places

Jan 15, 2009

I am using Excel 2003. I am attempting to use the Accounting format with numbers that should not have any decimal places (although what is entered might have a decimal place). The numbers line up fine on the right, however, the dollar signs on the left are not lining up. It looks something like:

View 4 Replies View Related

Setting Format Cell Accounting Using Vba

Mar 26, 2009

i am doing some calculations using vba in excel, i need to know how to set the cell format to 'accounting' in vba ?

View 2 Replies View Related

Modifying Accounting Custom Format?

Oct 31, 2013

I'm using this custom format

Code:
_-£* #,##0.00_-;[Red]_-£* #,##0.00_-;_-£* "-"??_-;_-@_-

How do I modify it so that if the value is 0.00 then 0.00 is showed and not the current "-"?

View 3 Replies View Related

Choose Different Accounting Number Format / Currency Symbol?

Feb 1, 2012

In the accounting number formats, the available currency symbols are Dollar ($), Pound (₤), Euro (€), and Yuan (¥). But how can I add a custom currency symbol? For example instead of writing "$1,000", I want to write "BDT. 1,000" or "৳ 1,000". How can I do that?

View 4 Replies View Related

Macro Recorded To Extract Data From Mainframe System?

Oct 12, 2012

I have following macro recorded to extract data from a mainframe system

open "C:DesktopReportRBDRBD.txt" for OUTPUT as #1
rc% = ps.sendstring("{clear}")
rc% = ps.sendstring("a24octdelbom*9w{enter}")
test1 = PS.getdata(450,3,27)
print #1,test1

The requirement here is run the above macro for the next 30 days automatically, for example

rc% = ps.sendstring("{clear}")
rc% = ps.sendstring("a25octdelbom*9w{enter}")
test1 = PS.getdata(450,3,27)
print #1,test1

and so on till 23 Nov. From the start date the macro should run for the next 30 days. Currently i am changing the dates manually in the macro

View 7 Replies View Related

Macro- Data That Is Exported Our House Investment System

Oct 27, 2008

i have a spreadsheet with data that is exported to Excel via our in house investment system, the report looks something like below, though real data consists of 2000 rows of data. Where we have O/S in Bank this means these entries are all physical bank entries i.e statement credit and statement debit, and where we have O/S not in Bank these are all accounting entries, i.e. Ledger Debit and Ledger Debit.

What i am after is a macro that will insert a column next to Team and then input SDR SCR on all statement entries and LDR and LCR on all Ledger entries, the final report should look like the second spreadsheet....

View 9 Replies View Related

Modifying Macro - Accounting Analysis

Jan 18, 2009

I have the macro shown below, which I found in a 2003 issue of the journal of accountancy - and it works great. However, it only works on a data set that begins in cell A1. I want to incorporate it into a spreadsheet I have where my data set begins in cell E15 and goes down from there(column E will be the only column that this macro will need to run on and I need it to work on a data set that will vary in length). This macro performs a Benford analysis, which analyzes the first and second number of a data set.

Dim Arrayone(0 To 9) As Integer
Dim Arraytwo(0 To 9) As Integer
Dim Arraythree(0 To 9) As Integer
Dim Arrayfour(0 To 9) As Integer
Dim Arrayfive(0 To 9) As Integer
Dim Arraysix(0 To 9) As Integer
Dim Arrayseven(0 To 9) As Integer
Dim Arrayeight(0 To 9) As Integer
Dim Arraynine(0 To 9) As Integer
Dim Arrayzero(0 To 9) As Integer
Dim Arraytwotest(10 To 99) As Integer

Dim x, I

Dim Row As Long, Col As Long, Step As Long, Colcells

Dim Digits As Long, Total As Long

View 9 Replies View Related

Develop A System Using Macro In MS 2007

Dec 12, 2007

I need to develop a System using Macro in MS Excel 2007.. but i don't know how to do it..

I got 2 file in MS Excel

File number 1 : Demand - file name : ( got 55675 row of data)
File number 2 : Supply - file name : ( got 6774 row of data )

File number 1 : The data is starting from 0110111101 ( commodity code ) until 9999999999 ( commodity code )

File number 2 : The data is starting from 1532423007(commodity code) until 9119199901 (commodity code )

I need to categorize all this data in 10 sheet ( sort by the first 2 digit of the commodity code ):

Sheet 1 : 00 -09
Sheet 2 : 10- 19
Sheet 3 : 20 - 29
Sheet 4: 30- 39
Sheet 5: 40 - 49
Sheet 6 : 50 - 59
Sheet 7 : 60 - 69
Sheet 8 : 70- 79
Sheet 9 : 80- 89
Sheet 10 : 90 - 99

View 14 Replies View Related

Create A Macro For A Monitoring System

Jan 7, 2009

I have no experience with macros at all but i need to create a macro for work that will keep the original data entered but work out the difference between new data collected each day ...

View 10 Replies View Related

Macro Slows System By Opening Many Windows

Jan 29, 2007

I've been using the following bit of code to run a macro at the specified time:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Application .OnTime TimeValue("12:00:00"), "GetData"
End Sub

The GetData sub executes a shell script and then pulls data from the result. The problem is that the macro runs 140+ times, and so I get 140 windows popping up and the system practically stops. I can't figure out why this is happening, as there are no loops or any sort of repetition in the code. Any help is greatly appreciated as this problem occurs with more than just the one spreadsheet.

View 9 Replies View Related

VBA - Macro Run When System Clock Changes - Forecasting With Live Feed?

Sep 21, 2013

I thought about using it for forecasting purposes. I might try to use it together with live data (temperature, seasonal patterns)... but if I graph it, it starts from left to right...

How to modify the code a bit...

VB:
Shift:=xlUp, CopyOrigin:=xlFormatFromRightOrDown

I tried to change the code, but it did not worked.

View 8 Replies View Related

Macro To Select Worksheet Based On System Date

Nov 21, 2012

I am thinking of commanding macro to select worksheet tab based on the system date to do some pasting.

My worksheet tab is as follow:
Nov-12, Dec-12, Jan-13, Feb-13 and .........

What should be the macro script required to select the worksheet tab based on today's date?

View 2 Replies View Related

Determine Date System Of Workbook In Which Macro Resides

Oct 24, 2007

I have written a few macros, which use date type variables. Because VBA uses the 1900 date system, and some workbooks use the 1904 date system, I have to first check and see if a workbook uses the 1904 date system, and if so, subtract off 1462 days where appropriate. My macros used to check the date system by using the command:

If ( ActiveWorkbook.Date1904 = True) Then
nh_stringToDate = nh_stringToDate - 1462
End If

But, sometimes the macro resides in one workbook (call it macro.xls) but the currently active workbook is some other workbook (something.xls). What I really care about is the date system of macro.xls and NOT something.xls. So, instead of "ActiveWorkbook" is there some other object I can use to refer to the workbook in which the macro itself resides? I don't want to rely on using the name of the macro workbook (macro.xls) because this could change! So I need a way to simply refer to the workbook in which the macro resides.

View 2 Replies View Related

Making Big Data System Into Table?

Jul 1, 2014

I have a big system of data, me not knowing better i have it such that the categories is listed in the start of the row, and new data for periods are listed in the columns following. when i mark the data and click table, it puts the scroll down thing on top of the columns, which is of no use to me at all, i need them at the start of the row. is this possible, or do i need to reorganize all the data, and VBA code used to sort and collect it ?

View 7 Replies View Related

Data Name Manager And Delete Raw 2 System

May 14, 2009

I have Given Name for Data Column as Below

[Data_Table=OFFSET(Data!$A$2,0,0,COUNTA(Data!$A$2:$A$28),COUNTA(Data!$1:$1))]

Now If I delete Raw 2 System is giving Error #Ref!
I may have to delete Raw-2 frequently

View 4 Replies View Related

Dynamic Ranges (data Exports From Another System)

May 19, 2009

I have a blank template spreadsheet that is copied every time I need to work on some data exports from another system (the data needs to be kept, hence the copying of the original blank template first).

I have set up conditional formatting using;

=MATCH($A1,range,0)

This formula is always in the entire column of row A in Worksheet1 (for the sake of argument)

Problem is in Worksheet2 where the range it's using to compare is always a different number of rows deep...it can be 70, it can be 2000. I always call the range 'interest'.

Is there anyway (in the blank template) of setting column A as a dynamic range called 'interest' so that after data import it automatically sets the range at the desired size?

View 2 Replies View Related

Excel Query Using Transfer Data From System?

Aug 12, 2013

I am looking for information on how I can use the Transfer Data from System i Add-In for Excel to only get data that equals the data in one of my columns (both sets of data are strings). Unfortunately, I cannot just get the entire contents of the System i table since it contains more than the maximum allowed in Excel.

View 1 Replies View Related

A Macro To Format Data

Aug 24, 2009

I have been trying to produce a macro that will format data of varying lengths. Basically, I pull data from a database and export it into excel. I have a macro that will format the first few lines fine. Unfortunately, there are a few lines of info at the end of each report that need to be deleted. I cannot figure out how to make excel reach the end of my data a delete these two lines. If I build the macro on one report that has 100 lines and delete line 99 and 100, and then attempt to run the macro on a report that has 150 lines - lines 99 and 100 are still deleted.

Is there a way to delete the last couple of lines of data of a spreadsheet?

View 9 Replies View Related

How To Format Data With A Macro

Jun 28, 2012

check and format some data with some code.

I have 100,000+ rows that look like this

Sheet1 *ABCDEFG1start_date start_time end_date end_timeTypeSOC Before SOC

After208/01/201218:36:2008/01/201218:40:00T**308/01/201218:45:1208/01/201218:50:44T**408/01/201218:52:1208/01/201219:50:46C3446508/01/201219:54:2708/01/201221:44:40C4681608/01/201221:48:4108/01/201222:02:36C8187708/01/201222:06:4608/01/201222:11:49C8787808/01/201222:15:5008/01/201223:02:00C87100909/01/201208:16:5109/01/201208:44:08T**1009/01/201208:45:1109/01/201208:49:55T**

I'm trying to merge all the "C" types in Column E into one row like this

Sheet1 *ABCDEFG14start_date start_time end_date end_timeTypeSOC Before SOC After1508/01/201218:36:2008/01/201218:40:00T**1608/01/201218:45:1208/01/201218:50:44T**1708/01/201218:52:1208/01/201223:02:00C341001809/01/201208:16:5109/01/201208:44:08T**1909/01/201208:45:1109/01/201208:49:55T**

So basically, I need to use the 'start date' and 'start time' from the first row and the 'start date' and 'start time' from the last row. Also use the first 'SOC before' and the last 'SOC' After'.

I have more data in column h - column z, so I need to delete the entire row of the additional "C" rows. Otherwise the data will be misaligned.

View 2 Replies View Related

Format Data With Macro?

Jul 22, 2012

Macro or formula to reformat some data.

I have like data like this:

Column A = Date
Column B = Single Distance
Column C = Total Distance for that date

Sheet1  ABC1DateIndividual Distance Total distance 204/01/2011919304/01/20111019405/01/2011
212505/01/20111012606/01/2011926706/01/2011326806/01/20111026906/01/20112261006/01/2011226

It would be really useful if I could format it like this

Sheet1  FGHIJ1Total Individual 219910  312210  42631022

So basically, it puts all the information on one row for a particular date.

View 7 Replies View Related

Format Data In Macro

Dec 15, 2006

I have cells which contain dates in the typical Excel format. IE: Jan 3rd, 2000 in unformatted form shows up as 36528 in Excel in Cell A2. What I would like to do, in VB, is take the contents of that cell A2 (36528), and turn it into a date in the form of:

Year,Month,Day
(For instance, Jan 5th 2001 would be
2001,1,5
And then assign it to a variable

View 4 Replies View Related

Coordinate System - Combine All Data In Single Graphic

Jan 3, 2012

I want to combine all data in a single graphic. It must be like a coordinate system.

The file is in that address: [URL] .......

View 1 Replies View Related

Macro To Move Data To Another Format

Feb 25, 2014

I'm looking for a macro to select data from a larger data set, and then put it in another worksheet. Basically, I want to select any cell in column A that contains "SO5" and move those to the column A of a separate worksheet. Then, for each cell that contains "SO5" would like to move to the right 5 columns and down 3 rows and select from that cell all the way to the end of the table. There may or may not be data in all of the table cells, so the selection should be based off of the column headers which will always have values. I would like to take that data and put it with the corresponding "SO5" in the new sheet.

Here's a sample:

MacroSample.xlsm

This is a simplified version, but I'm trying to get the data from sheet1 into the format in sheet2. Keep in mind that the number of columns and rows may change so it needs to be as dynamic as possible.

View 1 Replies View Related

Format Data To Another Sheet Macro

Feb 23, 2009

I import a raw data file from our phone system into sheet 'imported', I then want to display the data on sheet 'formatted' how I have displayed it for the purposes of this example. I had a solution to this but the formatting out of the phone system has changed.So I need it to list the agents names, and then the corresponding time for each activity. The activities are listed on B3:I3.

View 2 Replies View Related

Required Macro To Format The Raw Data

Sep 16, 2009

Iam just trying to format an raw data(which has morethan 40,000 rows) which is converted from notepad to excel.

The major problem the token number which is below the name and quantity i need to bring the number to left side of the row.

i think this has not given an clear picture so iam attaching an sample sheet

View 6 Replies View Related

Clear Data Not Format With Macro

Nov 7, 2009

I have a worksheet (A) that is populated from a second sheet (B). The data on A changes as necessary using another Macro. Most of the data on sheet B is numeric and comes from various formulas and results in numbers with too may digits after the decimal point. I can format sheet A to turn 58.22222222 into 58.2 but when I run a Macro to clear the data I lose the formatting. I need to find a way to clear only the data and not the format, or find a way to add a mask on specific columns in the macro that copies and pastes. Here is the Macro to clear the data.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved