XcelMonkey
New Member
- Joined
- Feb 18, 2015
- Messages
- 8
Hi,
I'll start off by mentioning that I'm very inexperienced when it comes to VBA, so this is may be quite simple.
I'm using Microsoft Excel 2013 and trying to make a macro that will create a chart based on a dynamic range of data. I have the macro written with all of my formats correct, and just need to make the range dynamic. I'll explain what I'm trying to do.
I have different hours of operation for each day of the week (Monday through Sunday), for each set of data that will be input into this template. I want my macro to create a chart, based on the hours of operation for a given day (e.g. Monday). The following is an example of what my data may look like for Monday:
I want the graph that this macro creates to be based only off the hours that this chart has values for e.g. 1pm-8pm (or call it A4:B11)
My original thought was to use an OFFSET function, referencing the 12PM cell (A3), and have the array stretch 1 column to the right, and a variable number of rows down, based on the hours (of operation) value held in another cell (Below as "8" under "# hrs" )
Note: I realized while trying to describe that my problem, that this would only solve the problem of determining the end of my array. I think I could implement a Match/Offset combination to solve this, but my formula would definitely get messy.
Now my problem is firstly that I'm sure there's a more efficient way to go about this - does anyone have a suggestion? If not, here's the line of code where I'm trying to input the previously stated function:
ActiveChart.FullSeriesCollection(1).Values = "=$A$3:OFFSET(G2,1)"
I'm well aware that there's probably a ton wrong with this line. To reiterate, I'm trying to create an array with the reference point "A3", extending "1" column to the right, and "G2" rows down.
This is my first post and I apologize if I'm not very clear.
Any help is greatly appreciated.
Thanks.
I'll start off by mentioning that I'm very inexperienced when it comes to VBA, so this is may be quite simple.
I'm using Microsoft Excel 2013 and trying to make a macro that will create a chart based on a dynamic range of data. I have the macro written with all of my formats correct, and just need to make the range dynamic. I'll explain what I'm trying to do.
I have different hours of operation for each day of the week (Monday through Sunday), for each set of data that will be input into this template. I want my macro to create a chart, based on the hours of operation for a given day (e.g. Monday). The following is an example of what my data may look like for Monday:
I want the graph that this macro creates to be based only off the hours that this chart has values for e.g. 1pm-8pm (or call it A4:B11)
My original thought was to use an OFFSET function, referencing the 12PM cell (A3), and have the array stretch 1 column to the right, and a variable number of rows down, based on the hours (of operation) value held in another cell (Below as "8" under "# hrs" )
Note: I realized while trying to describe that my problem, that this would only solve the problem of determining the end of my array. I think I could implement a Match/Offset combination to solve this, but my formula would definitely get messy.
Now my problem is firstly that I'm sure there's a more efficient way to go about this - does anyone have a suggestion? If not, here's the line of code where I'm trying to input the previously stated function:
ActiveChart.FullSeriesCollection(1).Values = "=$A$3:OFFSET(G2,1)"
I'm well aware that there's probably a ton wrong with this line. To reiterate, I'm trying to create an array with the reference point "A3", extending "1" column to the right, and "G2" rows down.
This is my first post and I apologize if I'm not very clear.
Any help is greatly appreciated.
Thanks.
