Board logo

標題: [發問] Excel vba get dynamic range( fields) to create the line chart [打印本頁]
作者: c3676816    時間: 2014-1-2 17:36     標題: Excel vba get dynamic range( fields) to create the line chart

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
[attach]17180[/attach]

sheet2
[attach]17181[/attach]
作者: c3676816    時間: 2014-1-2 17:48

I want to output the line chart:
[attach]17182[/attach]
作者: stillfish00    時間: 2014-1-3 09:27

回復 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    時間: 2014-1-3 10:38

回復 3# stillfish00


    Hello,stillfish00
I wrote  the VBA  code  ,Can you modify code for code segment in attachment
[attach]17186[/attach]
作者: Hsieh    時間: 2014-1-3 11:19

本帖最後由 Hsieh 於 2014-1-3 11:20 編輯

回復 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    時間: 2014-1-3 11:20

回復 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    時間: 2014-1-3 11:28

[attach]17187[/attach]回復 6# c3676816

it could be like this format
作者: GBKEE    時間: 2014-1-3 11:43

回復 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    時間: 2014-1-3 12:02

回復 8# GBKEE

hello,GBKEE,
I want to know how do you set the data source to create graph,
recompile have some wrong
作者: stillfish00    時間: 2014-1-3 13:40

本帖最後由 stillfish00 於 2014-1-3 13:43 編輯

回復 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
複製代碼
作者: GBKEE    時間: 2014-1-3 15:52

回復 9# c3676816
請附檔看看,為何錯誤
作者: c3676816    時間: 2014-1-3 17:44

@GBKEE
I fix it,thanks for your kindness
@stillfish00
THIS idea run at an independent excel file so perfect,thank you so much



歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)