none
Excel Macro RRS feed

  • 问题

  • 我想用Macro来算表格里的数据有多少行多少列,然后用数列来算每行每列的总数。但是Dim totalColIsarray(numbercol) as double   的时候会出,numbercol不是常量的错误。完整的代码如下,请指教

    Sheets("Sheet1").Select
    Range("A1").Select

    Dim introw As Integer
    Dim intcol As Integer

    introw = 1
    intcol = 1

    'Find out how many columns of data

    Do Until IsEmpty(ActiveCell.Value)
       If IsEmpty(ActiveCell.Offset(0, 1).Value) Then
       ' Do nothing
       Else
       intcol = intcol + 1
       End If
       ActiveCell.Offset(0, 1).Select
       
    Loop

    ' Define an array to hold totals

    Dim coltotal As Double
    Const intcol As Object = intcol
    ReDim coltotal(intcol) As Double

    ' Reset cell pointer to beginning of data

    Range("A1").Select

    ' Reset Column Count to 1

    intcol = 1

    Do Until IsEmpty(ActiveCell.Value)
       Do Until IsEmpty(ActiveCell.Value)
          If IsEmpty(ActiveCell.Offset(1, 0).Value) Then
          ' Do nothing
          Else
          introw = introw + 1
          coltotal(intcol) = coltotal(intcol) + ActiveCell.Value
          End If
          ActiveCell.Offset(1, 0).Select
          
       Loop
       
       If IsEmpty(ActiveCell.Offset(0, 1).Value) Then
       ' Do nothing
       Else
       intcol = intcol + 1
       End If
       ActiveCell.Offset(-introw, 1).Select
       
    Loop



    For numbercols = 1 To intcol
        ActiveCell.Offset(2, -intcol).Value = coltotal(intcol)
        ActiveCell.Offset(0, 1).Select
    Next

    • 已移动 Carl Cai 2014年3月5日 9:57 (移自VB论坛)更加相关且宏是一种vba语言 跟vb.net 差别很大
    2014年3月5日 7:23