[發問] 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

下載 (52.03 KB)

2014-1-2 17:34

sheet2

下載 (33.39 KB)

2014-1-2 17:36

c3676816

I want to output the line chart:

下載 (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)

下載次數: 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

複製代碼

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

下載 (34 KB)

2014-1-3 11:25

回復 6# c3676816

it could be like this format

GBKEE

回復 7# c3676816
是這樣嗎!

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

複製代碼

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

複製代碼