flbiker
01-21-2019, 11:15 AM
Hello VBA Gurus,
I am in need of some VBA help. I have dabbled a bit in want I would like to have, but I just can't warp my head around some of it.
I have an inventory of payments that I would really like to be a lot more automated. My process is to download a CC statement as a .CSV and move that data into my .xls workbook. Since this is a CC statement, the length can be from 2 to X Rows in length. My first problem is that some of the statements have the Positive and Negative reversed. Meaning, sometimes I have to go can changed every Positive to a Negative and every Negative to a Positive. When the statements get large, it becomes a nightmare.
My second problem is that I have not figured out a way to copy and insert the rows from the .CSV into my main .xls workbook. What I am doing is counting the number of rows, in the .CSV, inserting that amount plus an additional 20 rows (which I need), then copying the data from the .CSV to my .xls starting at row 110. The first line of each Payee will always start at row 110.
The data in my workbook is placed in yearly worksheets for auditing and tracing purchases. At the top of each CC statement (two rows above), in Column A is entered the Month Statement - Name Type CC #1234. (Example Sept Statement - Marys Main CC #1234). Can this be set up so I can enter what text I want each time? (thinking out loud, you would know best)
Then in the next row (one row above the Payee), under column E, is enter "Prev Balance" (fill color RGB(225, 225, 0) in yellow) and in Column F is "New Balance" (in yellow).
At the bottom of each CC statement I have the total for this statement. I place the total two rows below the last payee and color fill it RGB(201, 255, 102).
Under Columns K, L, & M, is created Formatted Cells for each Payee for notes. The box is formatted with an outside Border only and Horizontal lines in the center, no Vertical lines in the middle.
Requirements:
1) If needed, all numbers in Column E needs to be converted where all Negative Numbers are changed to Positive and all Positive numbers are change to Negative.
2) All numbers in Column E need to be Formatted as Currency, with 2 Decimal places, and Negative Numbers are Red and in Parenthesis.
3) Column E does not have a defined range. It can be 1 Row to X Row.
4) All data being transferred from .CSV to .xls will start at row 110.
5) Column C needs to be sorted by Payee and then by Date from Column A.
6) If the wording "PAYMENT - THANK YOU" is found, that Row will be placed at the top of the Payees list.
7) One row above all statements will be placed the wording "Prev Balance", in yellow (fill color RGB(225, 225, 0)) under Column E.
8) One row above all statements will be placed the wording "New Balance", in yellow (fill color RGB(225, 225, 0)) under Column F.
9) Two rows above all statements will be the Month Statement - Name Type CC #1234. Maybe an input box allowing me to enter text?
10) Two rows below the last Payee, under column E will be totaled, excluding the "PAYMENT - THANK YOU", if found, in light green (RGB(201, 255, 102).
11) Any cell not having a value, positive or negative shall be replaced with $0.00.
12) All Payees shall have Formatted Cells in Column K, L, & M. This box of cells will be Outline with a Horizontal, no Vertical line in the middle.
13) The Formatted Cells will be Filled with a Gray Background Color (Frist row, second box down under Background Color).
14) Column B is always hidden
After this I have to color code, enter notes, and bring previous totals in.... so much more.
When statements get large, it’s a bear. If someone can help me out, I would be grateful. :)
I will enclose a worksheet to show what the end results are. I think a visual will help.
I tried to word and described what I am looking for the best I can.
If this is way too much, please let me know.
If anything the converting of numbers would be the biggest help (#1).
Thank you for any and all help!!!!
Dave
I am in need of some VBA help. I have dabbled a bit in want I would like to have, but I just can't warp my head around some of it.
I have an inventory of payments that I would really like to be a lot more automated. My process is to download a CC statement as a .CSV and move that data into my .xls workbook. Since this is a CC statement, the length can be from 2 to X Rows in length. My first problem is that some of the statements have the Positive and Negative reversed. Meaning, sometimes I have to go can changed every Positive to a Negative and every Negative to a Positive. When the statements get large, it becomes a nightmare.
My second problem is that I have not figured out a way to copy and insert the rows from the .CSV into my main .xls workbook. What I am doing is counting the number of rows, in the .CSV, inserting that amount plus an additional 20 rows (which I need), then copying the data from the .CSV to my .xls starting at row 110. The first line of each Payee will always start at row 110.
The data in my workbook is placed in yearly worksheets for auditing and tracing purchases. At the top of each CC statement (two rows above), in Column A is entered the Month Statement - Name Type CC #1234. (Example Sept Statement - Marys Main CC #1234). Can this be set up so I can enter what text I want each time? (thinking out loud, you would know best)
Then in the next row (one row above the Payee), under column E, is enter "Prev Balance" (fill color RGB(225, 225, 0) in yellow) and in Column F is "New Balance" (in yellow).
At the bottom of each CC statement I have the total for this statement. I place the total two rows below the last payee and color fill it RGB(201, 255, 102).
Under Columns K, L, & M, is created Formatted Cells for each Payee for notes. The box is formatted with an outside Border only and Horizontal lines in the center, no Vertical lines in the middle.
Requirements:
1) If needed, all numbers in Column E needs to be converted where all Negative Numbers are changed to Positive and all Positive numbers are change to Negative.
2) All numbers in Column E need to be Formatted as Currency, with 2 Decimal places, and Negative Numbers are Red and in Parenthesis.
3) Column E does not have a defined range. It can be 1 Row to X Row.
4) All data being transferred from .CSV to .xls will start at row 110.
5) Column C needs to be sorted by Payee and then by Date from Column A.
6) If the wording "PAYMENT - THANK YOU" is found, that Row will be placed at the top of the Payees list.
7) One row above all statements will be placed the wording "Prev Balance", in yellow (fill color RGB(225, 225, 0)) under Column E.
8) One row above all statements will be placed the wording "New Balance", in yellow (fill color RGB(225, 225, 0)) under Column F.
9) Two rows above all statements will be the Month Statement - Name Type CC #1234. Maybe an input box allowing me to enter text?
10) Two rows below the last Payee, under column E will be totaled, excluding the "PAYMENT - THANK YOU", if found, in light green (RGB(201, 255, 102).
11) Any cell not having a value, positive or negative shall be replaced with $0.00.
12) All Payees shall have Formatted Cells in Column K, L, & M. This box of cells will be Outline with a Horizontal, no Vertical line in the middle.
13) The Formatted Cells will be Filled with a Gray Background Color (Frist row, second box down under Background Color).
14) Column B is always hidden
After this I have to color code, enter notes, and bring previous totals in.... so much more.
When statements get large, it’s a bear. If someone can help me out, I would be grateful. :)
I will enclose a worksheet to show what the end results are. I think a visual will help.
I tried to word and described what I am looking for the best I can.
If this is way too much, please let me know.
If anything the converting of numbers would be the biggest help (#1).
Thank you for any and all help!!!!
Dave