ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

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

[µo°Ý] 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


sheet2

I want to output the line chart:

TOP

¦^´_ 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.

TOP

¦^´_ 3# stillfish00


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

TOP

¥»©«³Ì«á¥Ñ Hsieh ©ó 2014-1-3 11:20 ½s¿è

¦^´_ 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
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 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

TOP

¦^´_ 6# c3676816

it could be like this format

TOP

¦^´_ 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
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 8# GBKEE

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

TOP

¥»©«³Ì«á¥Ñ stillfish00 ©ó 2014-1-3 13:43 ½s¿è

¦^´_ 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

TOP

        ÀR«ä¦Û¦b : ¡i®É¶¡¦¨´N¤@¤Á¡j®É¶¡¥i¥H³y´N¤H®æ¡A¥i¥H¦¨´N¨Æ·~¡A¤]¥i¥HÀx¿n¥\¼w¡C
ªð¦^¦Cªí ¤W¤@¥DÃD