VBA Help to Automate Plot Creation

apollop

New Member
Joined
Jul 12, 2021
Messages
1
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hello everyone,

Thank you for the time to read my post! I've just joined this forum as I'm looking for some help. I'm looking to start using Excel VBA to automate some plots on a big excel file I have where I place all my monthly spendings.

I have each month as a smart table, something like you see below:

DateAmountShopTypeType 2PercentageTotal Value
01/02/2021​
24.92​
QSTORESINGLESPLIT
12.46​
01/02/2021​
19.5​
RBIKESINGLENONE
0​
02/01/2021​
160​
SSTORESINGLESPLIT
80​
02/01/2021​
100​
TSTORESINGLESPLIT
50​
03/01/2021​
83​
DELECTRICITYMAINSPLIT
41.5​
03/01/2021​
174.04​
PSTOREHOBBIEOWN
174.04​
04/01/2021​
187.11​
CSTOREHOBBIEOWN
187.11​
06/01/2021​
129.91​
ASUPERMARKETMAINSPLIT
64.955​
06/01/2021​
27.39​
ASUPERMARKETMAINSPLIT
13.695​
06/01/2021​
623.63​
OHOUSESINGLESPLIT
311.815​
08/01/2021​
100​
NSTORESINGLENONE
0​
08/01/2021​
55.99​
HSTOREHOBBIEOWN
55.99​
09/01/2021​
42.75​
ASUPERMARKETMAINSPLIT
21.375​
10/01/2021​
119.66​
CSTOREHOBBIEOWN
119.66​
11/01/2021​
29.97​
ASUPERMARKETMAINSPLIT
14.985​
12/01/2021​
6.13​
ASUPERMARKETMAINSPLIT
3.065​
16/02/2021​
92.59​
ASUPERMARKETMAINSPLIT
46.295​
17/02/2021​
6.5​
MRESTAURANTEAT OUTSPLIT
3.25​
17/02/2021​
1.7​
EBANKMAINOWN
1.7​
18/02/2021​
163.35​
BSTOREHOBBIEOWN
163.35​
18/02/2021​
72.42​
BSTOREHOBBIEOWN
72.42​
18/02/2021​
23.41​
BSTOREHOBBIEOWN
23.41​
18/02/2021​
8.17​
LMEDICMAINOWN
8.17​
19/02/2021​
27.443​
ASUPERMARKETMAINSPLIT
13.7215​
19/02/2021​
235.33​
CSTOREHOBBIEOWN
235.33​
20/02/2021​
1.24​
ASUPERMARKETMAINSPLIT
0.62​
21/02/2021​
47.86​
ASUPERMARKETMAINSPLIT
23.93​
23/02/2021​
250.56​
BSTOREHOBBIEOWN
250.56​
24/02/2021​
48.05​
FWATERMAINSPLIT
24.025​
24/02/2021​
40​
GNETMAINSPLIT
20​
24/02/2021​
75.25​
CSTOREHOBBIEOWN
75.25​
24/02/2021​
52.9​
ASUPERMARKETMAINSPLIT
26.45​
24/02/2021​
24.01​
HSTOREHOBBIEOWN
24.01​
24/02/2021​
45.88​
CSTOREHOBBIEOWN
45.88​
25/02/2021​
50​
IRANDOMMAINSPLIT
25​
25/02/2021​
691.61​
USTOREHOBBIEOWN
691.61​
25/02/2021​
125.85​
VSTOREHOBBIEOWN
125.85​
25/02/2021​
23.97​
XSTOREHOBBIEOWN
23.97​
25/02/2021​
47.14​
CSTOREHOBBIEOWN
47.14​
26/02/2021​
121.9​
JMEDICMAINSPLIT
60.95​
27/02/2021​
23​
KRESTAURANTEAT OUTSPLIT
11.5​
27/02/2021​
8.58​
ASUPERMARKETMAINSPLIT
4.29​
28/02/2021​
78.33​
ASUPERMARKETMAINSPLIT
39.165​

The first row is the day of the month, the second one is how much was spent, the third one the place where the purchase was made, the fourth one I call it type 1, the 5th one type 2 (this one is more important), the 6th one is if I made the spending for myself, if it is split with someone or not and finally, the last one is how much I really spent after accounting for divisions etc.

I was looking to use VBA to create a script that would:

1 - Ask for the data range to be edited (on this example, I would select the whole table above) - and this would be stored in a variable
2 - Ask for the number of days of the month - I think this might be important as each month has a different number of days and I might not make a purchase on every single day
3 - Split the main table into 4 smaller tables, one for each category of type 2 spending (MAIN, HOBBIE, EAT OUT, SINGLE)
4 - On each table, leave only 2 rows - the date of the purchase and the total value
5 - Create a cumulative line plot for each table - a total of 4 line plots - that would plot how my daily spending increases as the month goes on (x axis the days of the month, y axis the value spent)
6 - Optional - a cumulative plot with the total of the 4 categories
7 - Place these plots underneath the table one after the other so I could later compare one month next to each other


Sorry for the lenghty question but I am used to another programming language. For simplicity I would like to implement all of this on Excel and also start learning VBA is an added bonus.

Can anyone help me with this?

Thank you very much!
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top