excel - 如何获取代码以正确计算一个电子表格中的项目（变量）并成功显示此信息？

Question

我需要我的 VBA 代码来计算某个电子表格（pc）上的所有“x”，然后将此信息传输到我正在创建的报告（rp）中以显示所有个人选择。目前，该代码标识了工作表上的所有 ID，但仅承认每个人的前 4 个选项，其中一些选项远不止这些。在这段代码的整个过程中，我对 K 列到 Y 列的选项进行了编辑，我认为这就是代码只承认未更改的选项的原因。我已经对代码进行了修改，但不知道如何更正此问题，以便成功显示所有选项。

任何帮助将不胜感激！

特定的电子表格代码将从

代码报告结果

rp.Cells(1, 1) = "Modules"
rp.Cells(1, 2) = "Student Count"
rp.Cells(1, 4) = "Students registered"
rp.Cells(1, 10) = "Students registered2"   'new

nRow = 2
For c = 2 To pc.Cells(1, Columns.Count).End(xlToLeft).Column
    rp.Cells(nRow, 1) = pc.Cells(1, c)
    rp.Cells(nRow, 2) = WorksheetFunction.CountIf(pc.Columns(c), "x")
    nRow = nRow + 1
Next c
      

rp.Cells(1, 1).CurrentRegion.Borders.LineStyle = xlContinuous
If rp.Cells(2, 4).Text <> "" Then
    rp.Cells(1, 4).CurrentRegion.Borders.LineStyle = xlContinuous
End If
rp.Rows(1).Font.Bold = True
rp.UsedRange.Columns.AutoFit

标签： excelvbaexcel-formula

Answer 1

尽管您的代码片段不足以确定问题的原因，但在操作数据时不与工作表交互肯定会有所收获。考虑以下示例作为替代方法：

Option Explicit
Sub consolidate()
    Dim arr, arrH
    With Sheet1
        arr = .Range("A1").CurrentRegion.Offset(1, 0).Value2 'get all data in memory
        arrH = .Range(.Cells(1, 1), .Cells(1, UBound(arr, 2))).Value2 'get the header in an array
    End With
    
    Dim j As Long, i As Long, ii As Long: ii = 1
    Dim arrC: ReDim arrC(1 To 1, 1 To UBound(arrH, 2)) '=> setup counter array
    Dim arr2: ReDim arr2(1 To UBound(arr), 1 To UBound(arr, 2)) '=> setup new array to modify source data
    For j = 1 To UBound(arr) 'traverse rows
        For i = 1 To UBound(arr, 2) 'traverse columns
            'here we can access each cell by referencing our array(<rowCounter>, <columnCounter>
            'e.g. arr(j,i) => if j = 1 and i = 1 we'll have the values of Cell A1
            'we can dump these values anywhere in the activesheet, other sheet, other workbook, ..
            'but to limit the number of interactions with our sheet object we can also use an intermediant arrays
            If arr(j, i) <> "" Then 'check if x
                arr2(j, ii) = arrH(1, i) 'replace x with the value from the header
                arr2(j, 1) = arr(j, 1) 'force the value in col1
                ii = ii + 1 'increment consolidated counter
                    
                arrC(1, i) = arrC(1, i) + 1 'increment sum
            End If
        Next i
        ii = 1 'reset consolidated counter for next line
    Next j
    'when we are ready with our data we dumb to the sheet
    With Sheet2 'the with allows us the re-use the sheet name without typing it again
        'the ubound function allows us to size the "range" to the same size as our array, once that's done we can just dumb it to the sheet
        .Range(.Cells(1, 1), .Cells(UBound(arrH, 2), 1)).Value2 = Application.WorksheetFunction.Transpose(arrH) 'transpose to get the col's in rows
        .Range(.Cells(1, 2), .Cells(UBound(arrC, 2), 2)).Value2 = Application.WorksheetFunction.Transpose(arrC)
        .Range(.Cells(1, 4), .Cells(UBound(arr2), UBound(arr2, 2) + 3)).Value2 = arr2
    End With
End Sub