Loop to select range of cells representing a month....

Okay, so I've never worked with Excel macros prior to today. I'm working on this for a friend. I've got this sheet that's going to a web address, importing a table from the page, and making a line graph of the data. I got all that to work fine (after a lot of trial and error). Now I've got the data, and I want to make a scatter plot of it... Here's the thing. I want each month of the data represented as a line on the plot. This is hard to explain. It looks like this:
\1
So from there, I want a loop to scroll through the dates and select a range that represents January, a range that represents February, etc, so I can save those ranges as variables and make a scatter plot with 12 curves on it.
Here is the code I have so far, in case you want to see it, not sure why you might need to though.

Please Login or Register  to view this content.

Hi

Can you put up an example file with your data and give some clues as to which range is to be selected for what group.


rylo

Well I don't have Excel on my home computer, just at my office, so I can't do that right now, but if you just make a macro out of the code I posted and run that, it'll go to the website and get the table and make the graph for you, so you can pretty much see what the document I have so far is. Only thing about that is that you'll need to change the url from "http://www.ustreas.gov/offices/domestic-finance/debt-management/interest-rate/yield.html" to "http://www.ustreas.gov/offices/domestic-finance/debt-management/interest-rate/yield_historical.html" to get the 2007 data since the 2008 data isn't complete yet. Then, I want it to select the range of all cells that represent January, save that range as a variable -- I don't know, lets say, JanuaryPlotRange. Then when I'm making a scatter plot, I can tell it to make a curve that represents only January data, i.e., JanuaryPlotRange. If this doesn't help, I'll get back to you in the morning. Thanks.

You know, I just realized I wasn't clear on something before that might make a huge difference. The macro goes to the site and gets the relevant table for me once a day. Each day, the table is updated with a new row and the corresponding date. So it is updated with one more row every business day. That's why I need a macro to update all of the information/graphs for me each day; so I don't have to do it all manually. If I had all the information already I could just manually set the data ranges. That may have been causing some confusion, sorry.

I'm at work now... not sure if you wanted me to actually give you the file or not, but here's a zip file that has the 2007 data.

To select the data is not difficult, but I am unclear what you are plotting as your graph what is the y axis and what is the x axis.

x axis day of the month?
x axis each of your current columns averaged or summed?

Regards

Dav

Well I wasn't clear on that because I figured once I got some help selecting the data I could do the rest. But I guess it won't help to get some extra help while I'm here.

The x-axis will have the time (in years) of the maturation length. So for anything less than one year (i.e., one, three and six month lengths), that will need to be divided by 12 to stay consistent. The y-axis is going to be the rates that are in the table.

HI

I'm also unclear on exactly what range of data you want to be able to select.

Is it a dynamic range that covers all the data in say column B?

Or do you want to break up column B into monthly blocks (B2:B22 for January, B23:B41 for February) then do some sort of calculation???

How about you provide some example ranges as I have done in the last para, and explain how / why they have been selected, and give formulas for any calculations that have to be performed on the ranges.

rylo

I don't really know how to explain it other than how I have...

Maybe this will help. I've attached a second file now that has an example file with a graph that I want, but the ranges of the data on that graph are not correct. Just format.

As for ranges, in the first file that I attached for you guys, I would like the ranges selected to be:

A2:L22 (January); A23:L41 (February); A42:L63 (March); etc etc.

I could do it easily by telling it to do those specific cells if I had all the data. But as I said before, it adds a new row of data every day that the site updates. So say we're in the middle of March. Row #63 will not be there yet; therefore, I just want the macro to grab A42:L57, or where ever in March we happen to be on that day.

Does this help?

Thanks for bearing with me.

Hi

Ok starting to get the idea.

On your first file, go to row 58 and delete all the data from that point down. The run the following code.

Please Login or Register  to view this content.

It should create a series of defined names in the form mmmyy.

If you then add some more rows of the original data, and rerun the code, it should update the names, and add a new one(s) if you move to the next month(s).

This is all based on the dates in column A.

If that is working OK,then we can move on to establishing the names for the data in the columns.

rylo

How do I know if it's working okay? I run the code, but nothing happens. I looked at the code to figure out what I could, but I'm not sure what it's supposed to be doing...

Hi

Go insert, name, define and have a look at the names that have been created.

Alternatively go to the name box (the drop down box above column A), and select the dropdown. There should be a series of names. Select one and see if it goes to the correct date block for the name.


rylo

Yeah, it's made Feb07, Jan07, yield_1, yield_2, yield_3, and yield_4. But the yields may have been there already. Is this what should be happening?

Hi

I don't know about the yield_? names, but the Jan and Feb are new entries.

Add additional data rows (say covering March and April), rerun the code, and see if the new names appear. Select one of the names, and make sure it covers the relevant date range.

If that is all OK, then we can move on to creating names for the data columns.


rylo

If I keep all of the data in there -- January 1st through December 31st -- it gives me the names Apr07, Aug07, Feb 07, Jan 07, Jul07, Jun07, Mar07, May07, Nov07, Oct07, Sep07. Looks like they put them in alphabetical order, but there's no Dec07... why not?

Hi

Why not? 'cos I fluffed it...

I've updated the code to include Dec and also created the defined names for the other items

So Dec07 will cover the date range for December 2007, and Dec07_1mo will cover the block B233:B255. These additional names will only be visible via Insert, Name, Define.

However you can use these names to build your graph ranges.

Please Login or Register  to view this content.

HTH

rylo

Alright, I'm at work and don't have a lot to do right now, hopefully I can figure out what this code is doing and use it to make the graphs... I'll be back on here if I have any more questions, which is very probable.

Thanks for the help.

Hi

Been mulling on this and don't really see the need to create all the named ranges.

Is this to work on the graph in the example file, or are you planning to build a new graph that shows a monthly view, based on a user selected month? If so, it would be easier to build the graph, then use a series of standard defined names that are relative to the selected month.

How about a bit more detail on the use of the monthly named ranges - there may be an easier way.

rylo