使用VBA excel(动态行,列)为每行创建折线图 [英] Creating Line Chart for each Row using VBA excel (Dynamic Row,Column)
问题描述
如果有人可以使用这段代码帮助我,我将不胜感激。
I would appreciate if someone can help me with this code
- 我有动态行和列,代码使用LastRow查找行数和列数, LastColumn。我必须绘制每一行的线形图(将coulmn固定在找到的数字),并将其放在表2中。我创建了一个带有记录和循环的混合代码(因为我是一个新的编码)。我必须绘制的excel表格表如下(它可以是行和列都是动态的,单元格,计数器等都是标题,第一行是A,Nbr等)。请帮助
-- I have dynamic rows and columns, the code finds number of rows and columns using LastRow, and LastColumn. I have to plot line chart for each row (keeping the coulmn fixed at the number found) and place it in sheet 2. I created a hybrid code with recording and looping (As i am a new to coding). The excel sheet table i have to plot is given below (and it can be dynamic both in rows and columns. Cell, Counter etc are header, First row is A ,Nbr, etc ). Please Help
细胞计数器0:45 1:00 1:15 1:30 1:45 2:00 2:15 2:30
A Nbr 10 54 45 0 0 0 0 0
Cell Counter 0:45 1:00 1:15 1:30 1:45 2:00 2:15 2:30
A Nbr 10 54 45 0 0 0 0 0
Dim i As Long
Dim LastRow As Long
Dim LastColumn As Long
Dim cht As Chart
LastRow = Range("A65536").End(xlUp).row
LastColumn = Range("A1").End(xlToRight).Column
For i = 2 To LastRow
Dim location As String
Range("$A$i:$LastColumn").Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlLine
ActiveChart.SetSourceData Source:=Range("Sheet1!$A$i:$LastColumn")
With ActiveChart.Parent
.Height = 225 ' resize
.Width = 500 ' resize
ActiveChart.ChartArea.Copy
Sheets("Sheet2").Select
ActiveSheet.Pictures.Paste.Select
Sheets("Sheet1").Select
Application.Run ("DeleteEmbeddedCharts")
End With
Next i
End Sub
推荐答案
请尝试下面的代码
Sub main()
'variable declaration
Dim i As Long
Dim LastRow As Long
Dim LastColumn As Long
Dim chrt As Chart
'Find the last used row
LastRow = Sheets("Sheet1").Range("A65536").End(xlUp).Row
'Find the last used column
LastColumn = Sheets("Sheet1").Range("A1").End(xlToRight).Column
'Looping from second row till last row which has the data
For i = 2 To LastRow
'Sheet 2 is selected bcoz charts will be inserted here
Sheets("Sheet2").Select
'Adds chart to the sheet
Set chrt = Sheets("Sheet2").Shapes.AddChart.Chart
'sets the chart type
chrt.ChartType = xlLine
'now the line chart is added...setting its data source here
With Sheets("Sheet1")
chrt.SetSourceData Source:=.Range(.Cells(i, 1), .Cells(i, LastColumn))
End With
'Left & top are used to adjust the position of chart on sheet
chrt.ChartArea.Left = 1
chrt.ChartArea.Top = (i - 2) * chrt.ChartArea.Height
Next
End Sub
这篇关于使用VBA excel(动态行,列)为每行创建折线图的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!