How to draw a conditional graph in excel using vba?

**somani123** · 03-31-2015, 01:30 PM

Hello!

I need a help to draw a conditional graph in excel using vba.

Excel Worksheet1: Statement Tool,
SheetName: Merchant: Creating a tool. In this I am entering the TID No and taking data from 3 different sheets based on the TID No and final calculation is showing on this.

Excel Worksheet2: Net Earnings Log
SheetName: Net Earnings : Displayed with TID No and Monthly earnings as in the sheet.

I have completed till the calculation part.

Now I want as per the TID No. the last 12 months data should be selected and the graph should display on sheet Merchant.

Kindly help on this.

Thanks a lot in advance.

**somani123** · 04-01-2015, 10:36 AM

Can anyone help me on the above issue?

Kindly help me.

**djapigo** · 04-01-2015, 03:19 PM

Sorry, I can't help with your information... it would help if you add more data (fake info, of course) and sample results that you want to see...

Post again for everyone to see.

**somani123** · 04-01-2015, 03:28 PM

Hello!

I want to go and search the TID no in Net Earning Log sheet.

When the TID no match, I want to create the graph for the last 12 months rather we copy and paste in Statement Tool or not.

Just want a graph to be displayed in sheet named with Merchant.

**MrShorty** · 04-02-2015, 02:09 PM

I want to go and search the TID no in Net Earning Log sheet.

In a divide and conquer sense, I would probably start by focusing on this step.

Usually when I want to "search" a list/range/database, I will use the MATCH() function. https://support.office.com/en-us/art...9-533f4a37673a

The MATCH() function is accessible to VBA: http://www.cpearson.com/excel/Callin...ionsInVBA.aspx

The MATCH() function will tell you which row contains the TID no. Then you can use the INDEX() function (in Excel) or the Cells(i,j) method in VBA to extract the desired data from that row.

I might use the MATCH() function again on the "dates" row to tell me which row corresponds to the current month, then count backwards 12 cells/months from that to get the values for the most recent 12 months.

An alternative approach might be to use Filters. The help file for the Range object in VBA (https://msdn.microsoft.com/en-us/lib...ice.15%29.aspx ) has sample code provided by Dennis Wallentin that uses the Advanced Filter method to extract data from one list and paste into a range in another sheet.

Once you have the data extracted from the source database, creating the chart should be straightforward.