[µo°Ý] Excel vba get dynamic range( fields) to create the line chart

c3676816

I have  the same problem on stackoverflow
I export an excel with a table in sheet1,and have a graph in sheet2, but now I have one problem, the table was generated from store procedure,the fields:"2013/Q1 2013/Q2 2013/Q3 2013/Q4 " was dynamic AND also the statistic values which display by user choose from webpage,now,the exported graph (Line Chart) ,its X-axis includes the statistic values that I dont need the fields( sheet2 ),
I want to get the range "B1" to "E11"( the cell is dynamic,so we dont get the position of the cell exactly), and all the title fields and data of row and column were dynamic , how can I write the correct VBA to do it, thanks
sheet1

¤U¸ü (52.03 KB)

2014-1-2 17:34

sheet2

¤U¸ü (33.39 KB)

2014-1-2 17:36

c3676816

I want to output the line chart:

¤U¸ü (37.07 KB)

2014-1-2 17:44

stillfish00

¦^´_ 2# c3676816
Try to define a name of dynamic range : =OFFSET($B$1,0,0,COUNTA($B:$B),COUNTA($1:$1)-6)
Then set the source of your chart refer to this name.

c3676816

¦^´_ 3# stillfish00


    Hello,stillfish00
I wrote  the VBA  code  ,Can you modify code for code segment in attachment
myexp.rar (16.56 KB)

¤U¸ü¦¸¼Æ: 8

2014-1-3 10:32

Hsieh

¦^´_ 2# c3676816

1. Sub ex()
   
2. With Sheet1
   
3.    With .Shapes.AddChart(xlLine)
   
4.       .Chart.SetSourceData .Parent.[A6:E16]
   
5.       .Chart.PlotBy = xlRows
   
6.     End With
   
7. End With
   
8. End Sub

½Æ»s¥N½X

c3676816

¦^´_ 5# Hsieh
Thanks for your idea ,the last column is located at "E column" (dynamic, maybe F,G...column),
because the statistics could not display,and the field like "2013/Q3",that have 2 type, the other type like "2013/4m",
so I want to get the range from "B1" to the last address of column type1 or type2

c3676816

¤U¸ü (34 KB)

2014-1-3 11:25

¦^´_ 6# c3676816

it could be like this format

GBKEE

¦^´_ 7# c3676816
¬O³o¼Ë¶Ü!

1. Function FindRange()
   
2.     Dim i As Integer, Rng As Range
   
3.     Range("B6").Select
   
4.     Set Rng = Range(Selection, Selection.End(xlToRight))
   
5.     For i = 2 To Rng(Rng.Columns.Count).Column - Rng(1).Column + 1
   
6.         If Rng.Cells(i) = "SUM" Then
   
7.             Exit For
   
8.         End If
   
9.     Next i
   
10. End Function
    
11. Function Ex()
    
12.     Dim E As Range, Rng As Range
    
13.     Set Rng = Range(Range("B6"), Range("B6").End(xlToRight))
    
14.     For Each E In Rng
    
15.         If E = "SUM" Then Exit For
    
16.     Next
    
17. End Function

½Æ»s¥N½X

c3676816

¦^´_ 8# GBKEE

hello,GBKEE,
I want to know how do you set the data source to create graph,
recompile have some wrong

stillfish00

¦^´_ 4# c3676816

1. Sub Macro1()
   
2.     Dim oReg As Object, lCol As Long
   
3.    
   
4.     Set oReg = CreateObject("vbscript.regexp")
   
5.     oReg.Pattern = "^\d{4}/Q?\d{1,2}"
   
6.       
   
7.     With Sheet1
   
8.       For lCol = 2 To .Range("B6").End(xlToRight).Column
   
9.         If Not oReg.test(.Cells(6, lCol).Value) Then Exit For
   
10.       Next
    
11.       Sheet2.ChartObjects("Chart 1").Chart.SetSourceData _
    
12.         Source:=.Range(.Range("A6"), .Cells(6, lCol - 1)).Resize(.Range("B6").CurrentRegion.Rows.Count), _
    
13.         PlotBy:=xlRows
    
14.     End With
    
15. End Sub

½Æ»s¥N½X