[已解决]自动识别数据源最后一列，我用cloumns.count不行

棋行天下黄

Dim rng As Range
Dim STR As String
STR = InputBox("请输入最后一列的英文标识如M,G等")

   Set rng = Range("A3:" & STR & "7")

   MsgBox (rng.Address)
   rng.Select
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlColumnClustered
    MsgBox (rng.Address)
    ActiveChart.SetSourceData Source:=Range("Sheet1!$A$3:" & rng.Address)
    ActiveChart.Legend.Select
    ActiveChart.Legend.LegendEntries(3).Select
    ActiveChart.SeriesCollection(3).ChartType = xlLineStacked100
    ActiveChart.Legend.Select
    ActiveChart.Legend.LegendEntries(4).Select
    ActiveChart.SeriesCollection(3).Select
    ActiveChart.SeriesCollection(3).AxisGroup = 2
    ActiveChart.Legend.Select
    ActiveChart.Legend.LegendEntries(3).Select
    ActiveChart.SeriesCollection(4).ChartType = xlLine
    ActiveChart.Legend.Select
    ActiveChart.Legend.LegendEntries(3).Select
    ActiveChart.SeriesCollection(4).Select
    ActiveChart.SeriesCollection(4).AxisGroup = 2
   ActiveChart.ChartArea.Select

    ActiveChart.SeriesCollection(4).Select
    ActiveChart.SeriesCollection(4).ApplyDataLabels
    ActiveChart.SeriesCollection(4).Points(8).Select
    ActiveChart.SeriesCollection(4).Points(7).Select
    ActiveChart.SeriesCollection(1).Select
    ActiveChart.SeriesCollection(1).Points(7).Select
    ActiveChart.SeriesCollection(1).Points(7).ApplyDataLabels
    ActiveChart.SeriesCollection(3).Select
    ActiveChart.SeriesCollection(3).ApplyDataLabels
    Range("N36").Select
End Sub

我把 Set rng = Range("A3:" & STR & "7") 写成range（cells(3,1),cells(7，cloumns.count）.end（xltoleft）不行，没把法才用了INPUTBOX，是什么原因呢？

最佳答案

月排行榜 / 总排行榜

sgxb123431380

2013-10-9 08:46

    Dim rng As Range
    Dim STR As String
   
    Set rng = Range(Cells(3, 1), Cells(7, Columns.Count).End(xlToLeft))
    MsgBox (rng.Address)
    rng.Select
   
    ActiveSheet.Shapes.AddChart.Select
    With ActiveChart
        .ChartType = xlColumnClustered
        MsgBox (rng.Address)
        .SetSourceData Source:=Range("Sheet1!$A$3:" & rng.Address)
        .SeriesCollection(3).ChartType = xlLineStacked100
        .SeriesCollection(3).AxisGroup = 2
        .SeriesCollection(4).ChartType = xlLine
        .SeriesCollection(4).AxisGroup = 2
        .SeriesCollection(4).ApplyDataLabels
        .SeriesCollection(1).Points(7).ApplyDataLabels
        .SeriesCollection(3).ApplyDataLabels
    End With
   
    Range("N36").Select

跳转到最佳答案楼层

我心飞翔410

Cells(3, Columns.Count).End(xlToLeft).Column   '取第三行的最后一列的列号

danio112

我把 Set rng = Range("A3:" & STR & "7") 写成range（cells(3,1),cells(7，cloumns.count）.end（xltoleft）不行，没把法才用了INPUTBOX，是什么原因呢？

这种写法是可以的，你的问题是，最右边少了一个")", 并且单词写错了一个，把set 也去掉。
rng = Range（cells(3,1),cells(7, columns.count).end（xltoleft))

sgxb123431380

    Dim rng As Range
    Dim STR As String
   
    Set rng = Range(Cells(3, 1), Cells(7, Columns.Count).End(xlToLeft))
    MsgBox (rng.Address)
    rng.Select
   
    ActiveSheet.Shapes.AddChart.Select
    With ActiveChart
        .ChartType = xlColumnClustered
        MsgBox (rng.Address)
        .SetSourceData Source:=Range("Sheet1!$A$3:" & rng.Address)
        .SeriesCollection(3).ChartType = xlLineStacked100
        .SeriesCollection(3).AxisGroup = 2
        .SeriesCollection(4).ChartType = xlLine
        .SeriesCollection(4).AxisGroup = 2
        .SeriesCollection(4).ApplyDataLabels
        .SeriesCollection(1).Points(7).ApplyDataLabels
        .SeriesCollection(3).ApplyDataLabels
    End With
   
    Range("N36").Select

ppp710715

打红色的地方错误（cloumns应为Columns）：
range（cells(3,1),cells(7，cloumns.count）.end（xltoleft）应改为：
Set rng = Range(Cells(3, 1), Cells(7, Columns.Count).End(xlToLeft))

美斯特邦威

1. range(cells(3,1),cells(7,cells(7,cloumns.count).end(1).column))

复制代码