Ditutup

Macro that will create two tabs in Microsoft Excel

Macro Specifications for Shorefront Y LINKS import into Sage Fund Accounting

The Macro will be designed as a button that, when pressed, will create two identical import tabs within Microsoft Excel based off the information contained in the “Payment Method” column of the original data file.

If the Payment Method column contains the text “Credit”, create an import tab with Session ID “CCYYMMDD”.

If the Payment Method column contains the text “Cash”, create an import tab with the Session ID CRYYMMDD.

The date portion [YYMMDD] of the Session ID’s on both import tabs will be the system date.

If the Macro encounters a blank cell in the “Payment Method” column, it should ignore all of the associated data in that row.

On each import tab, the Macro will default the following fields to columns based on specific criteria: Session ID, Session Status, Session Date, Session Description/Document Description, Effective Date, and Bank Deposit number.

• Session ID for each import tab will contain the specific information mentioned above, depending on the criteria in the “Payment Method” column.

• The Session Status column will be the same on both import tabs. This column will contain “BP” for Batch-To-Post, on each transaction line.

• The Session Date column will be the same on both import tabs, and will be driven by the system date. All of the transactions will contain the same Session Date.

• The Session Description and Document Description columns will contain the same information: “LINKS System Import.” This will be identical on both the cash and credit import tabs.

• The Effective Date will be based off of the “Date” column, which in turn will need to be modified by the macro to a MM/DD/YYY format. The date currently residing in the “Date” column will make up the “Effective Date” on both import sheets.

• The Bank Deposit number column will draw its information from three columns: The “Till” column the “Date” column and the “Payment Method” column, combining the three to read LKSA020212CR (as a credit card transaction example). If it is a credit card transaction a CR will be added, which a CA will be added for cash at the end of the bank deposit number.

Note: The four columns listed above will begin directly after the “Credit” column on both of the import tabs.

Both the “Credit” and “Cash” import files that the Macro creates will be one sided transaction entries. In other words, there will only be a “Credits” column with no corresponding “Debits” column.

The data for this column on the import sheets will be drawn directly from the “Amounts” column on the data sheet. When the macro is reading the numerical amounts, it cannot have any currency indicators or commas. For example, 2100.45 is an acceptable numerical import while $2,100.45 is not and the import will fail as a result. Offsets will be applied during the import into Sage.

The Transaction Description column on each import file will be a combination of the “Number” “Code” “Description” “Cust Number” and “Cust Name” columns. The macro will need to read the information contained in each column, separating it with a – when the macro begins to read the next column. However, due to Sage Fund Accounting length restrictions, the macro needs to truncate this information at 75 characters. An example of what the Transaction Description column will need to look like: 1-S002-Group-1138-ELINA BERRY, making sure this is kept under 75 characters on both import sheets.

The macro will need to add two additional columns to the end of each import tab. These columns are “Transaction Source” and “Transaction Type”. For each entry, the “Transaction Source” column will contain CR, and the “Transaction Type” column with contain a N. This information will be the same on both import tabs.

Because Sage required that Cash Receipts need to have full segment coding, the Macro will need to create 5 columns on both import tabs. These columns will be “Fund” “Department” “GL” “Prog” and “Grant”.

• The “Fund” column on both import tabs will be the same for every transaction and will default to 01.

• The “Department” column on both import tabs will default to 480 for all associated transactions.

• The “GL” column on both import tabs will default to 4710 for all associated transactions.

• The “Program” column will not default to identical values for each row on both import tabs. It will use each rows data within the “Code” column on the data file to populate the “Program” column on the import tabs.

• The “Grant” column on both import tabs will default to 12345 for all of the associated transactions.

Below are two screenshots of what the import tabs should look like after the macro has been run. These will be the columns that will populate each import tab, and the corresponding data that will match with each transaction from the original data file.

The below description is based off of a previously created workbook, that once the project has been accepted will be sent to the programmer.

Kemahiran: Pengaturcaraan C#, Pengaturcaraan C++, Java, Perl, Visual Basic

Lihat lebih lanjut: sheets import, sage macro coding, will draw, what is a line card, type of accounting system, tab bank, source document in accounting, sheets add, on line accounting, one tabs, microsoft number, microsoft excel group, line line segment, java and excel, information in accounting, data entry macro, credit one bank, cash#4, ca in accounting, bank id type, associated bank, applied bank, what is macro in excel, what is a pending transaction, microsoft macro

Tentang Majikan:
( 1 ulasan ) Mendham, United States

ID Projek: #1532258

13 pekerja bebas membida secara purata $125 untuk pekerjaan ini

bharanisrihari

Hello, Excel VBA expert here. Please check PM for details. Thanks!

$150 USD dalam 3 hari
(31 Ulasan)
5.7
Rahul2012

[url removed, login to view]

$100 USD dalam 4 hari
(25 Ulasan)
5.4
vserv

10+ years experience with VBA. Pls see your messages. Thanks

$50 USD dalam 3 hari
(29 Ulasan)
4.9
northwolves

Ready to [url removed, login to view] more details,please.

$100 USD dalam 2 hari
(35 Ulasan)
4.8
microk

Expert here.. Please check PM..

$100 USD dalam 3 hari
(11 Ulasan)
3.7
hajhouj

I have a good experience in VBA programming

$50 USD dalam 2 hari
(3 Ulasan)
3.1
Ql4I1AWw0

Check PMB please.

$250 USD dalam sehari
(0 Ulasan)
0.0
shagoon2u

VBA Experts are ready to start now

$200 USD dalam 2 hari
(0 Ulasan)
0.0
dipalp

I am a Production Planner in my day job, with former experience in IT, and am very well versed with Excel Macros. From the description that you provided, I believe I can have the work complete within five day's turnaro Lagi

$200 USD dalam 5 hari
(0 Ulasan)
0.0
sumshank

Thanks for your time in reading my bid. Here is what I can offer: - Excellent MS Excel skills, having used it extensively during my work with the Information Technology industry. - My Excel work includes usage of Lagi

$150 USD dalam 5 hari
(0 Ulasan)
0.0
JeffManross

I have been coding in VBA 1996. Started using Office 5.0 on WFW 3.11 and Windows95. Thank for you time. jeffmanross - hotmail - com

$125 USD dalam 2 hari
(0 Ulasan)
0.0
FirestarterXIII

Hello, I'm experienced with VBA specially for MsOffice products. It'll be piece of cake. It would be great if I could provide you sample work. Please contact me.

$50 USD dalam 5 hari
(0 Ulasan)
0.0
MarcoDong

experienced on Excel Macro

$100 USD dalam 2 hari
(0 Ulasan)
0.0