vba - 使用VBA excel(动态行，列)为每一行创建折线图

Question

如果有人可以帮助我使用此代码，我将不胜感激

-- 我有动态行和列，代码使用 LastRow 和 LastColumn 查找行数和列数。我必须为每一行绘制折线图(将库姆固定在找到的数字上)并将其放在表 2 中。我创建了一个带有记录和循环的混合代码(因为我是编码新手)。下面给出了我必须绘制的 excel 工作表(它可以在行和列中都是动态的。单元格、计数器等是标题，第一行是 A、Nbr 等)。请帮忙

细胞计数器 0:45 1:00 1:15 1:30 1:45 2:00 2:15 2:30
编号 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          

Answer 1

试试下面的代码

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