i have a code that gets calculates rolling data and then charts the results. when i run it i get run-time error 91. here’s the error line: ActiveChart.SetSourceData Source:=Sheets(“Sheet1”).Range(Cells(RollMonth + 2, 4), _ Cells(TradingDays, 4)) If I use Range(“xx:yy”) i get no error and the code runs smoothly. But i can’t use it as I have to change range manually every time i change the size of the window. what am i doing worng? VBA gurus please help your brother.
Try putting ‘on error resume next’ before the code. After the code put ‘on error goto 0’ to turn back on normal error checking. W/O seeing all the code, that’d be the first thing to try.
Easiest thing to do is creat a dynamic named range and reference that in your code. That is what I always did since it makes everything cleaner and it has always worked well. Let me know if you need help with the dynamic named range.
that would be awesome eureka! thanks packattack, actually it partially worked, now the problem is it creates an embedded chart with nothing in it, i think the reason is how i define range, it messes up when i use variables in ranges.
K, if you are using 2007 go into Formulas -> Name Manager and select new. In the source field you can use the below code to make the range dynamic using the offset and countif functions. You need to have some kind of condition so that it knows where to end the range. Take a look at the below code and you should be able to put it together. =Sheet1!$a$1:$BI$6,OFFSET(Sheet1!$a$1,0,60,6,60-COUNTIF(Sheet1!$A$1:$BI$6,"")) Then save your named range and call it something, like “Named Range”. Then below is your VBA code. .SetSourceData Source:=Sheets(“Sheet1”).Range(“Named Range”)
thanks eureka. I have a couple questions. can I shoot you an email?