时间戳

首页 > 解决方案 > Excel循环遍历第1行中的所有填充单元格

excel - Excel循环遍历第1行中的所有填充单元格

问题描述

我确定这是可能的，我只是不确定代码应该是什么。我有 2 张工作表：（1）组件，其中包含分析师标记的所有组件名称，包括调用发生的日期，以及（2）计算器，它计算特定组件在特定组件中出现的次数周数。

我创建了一个代码，该代码从组件表中获取不同的组件名称，然后将它们复制并转置到计算器表中。所有组件名称都在第 1 行，从 D1 列开始，然后到 E1、F1 等。我希望第 2 行显示组件（列于第 1 行）在一周内出现的计数或次数。

我的代码只适用于列，我不知道如何让它获得整行的非空值。

'//这里是我用来将不同组件从组件表转置到计算器表的代码

Public Sub GetDistinctComponents()
Application.ScreenUpdating = False

Dim lr As Long
    lr = Sheets("Components Data").Cells(Rows.Count, "F").End(xlUp).Row
    Sheets("Calculator").Unprotect Password:="secret"
    Sheets("Components Data").Range("F1:F" & lr).AdvancedFilter Action:=xlFilterCopy, _
    CopyToRange:=ActiveSheet.Range("DW1"), Unique:=True

    With ThisWorkbook.Worksheets("Calculator")
    .Range(.Range("DW1"), .Range("DW1").End(xlDown)).Copy
    .Range("DX1").PasteSpecial xlPasteValues, Transpose:=True
    .Columns("DW").EntireColumn.Delete
End With
Sheets("Calculator").Protect Password:="secret", DrawingObjects:=False
End Sub

这是我的组件表 组件数据

下面是我的计算器表。如您所见，转置不同组件的代码工作正常。我只是不知道如何从 DX 开始获取第 1 行的值，因此我可以将其存储在一个变量中，我将使用该变量来计算该组件在一周内出现的次数。我认为它应该像这样 Component = wsCalculator.Cells(i, "D").Value 但此代码仅在我想获取 D 列中所有单元格的值时才有效，而不是旁边的单元格的值D1

计算器

这是我目前拥有的代码

Public Sub CountComponent()
Application.ScreenUpdating = False
Sheets("Calculator").Unprotect Password:="secret"
Set wsComponentData = Sheets("Components Data")
Set wsCalculator = Sheets("Calculator")
Dim ComponentCount As Integer

'//Get the index of the last filled row based on column A
LastComponentRowIndex = wsComponentData.Cells(Rows.Count, "A").End(xlUp).Row

'//Get Range for ComponentData
Set ComponentRange = wsComponentData.Range("F2:F" & LastComponentRowIndex)

'//Get the index of the last filled row based on column C
LasttotalauditRowIndex = wsCalculator.Cells(Rows.Count, "C").End(xlUp).Row

'//Get range for Calculator
Set MyRange = wsCalculator.Range("C2:C" & LasttotalauditRowIndex)
TotalCalls = WorksheetFunction.Sum(MyRange)

'//Looping through all filled rows in the Components Data sheet
For i = 2 To wsCalculator.Cells(Rows.Count, "A").End(xlUp).Row

'//Get Component from cell in column "DW"
    'Component = wsCalculator.Cells(i, "DW").Value

    '//Count the # of calls that got hit in the corresponding Component
    If wsCalculator.Cells(i, "DW").Value <> "" Then
    ComponentCount = Application.WorksheetFunction.CountIf( _
    ComponentRange, component)
    wsCalculator.Cells(i, "DX").Value = ComponentCount
    End If
Next
End Sub

标签： excelvbaloops

解决方案

我建议看一下 VBA 字典。在这种情况下，您可以将每个组件存储为一个键，对于值，您可以累积该组件在给定周内的出现次数。

目前我的计算机上没有可用的 VBA 编辑器来测试它，但它可能看起来与我下面的内容相似。另外，我承认我可能没有完全理解你的床单的布局，但这里的一般原则肯定会适用。

有关 VBA 中字典的完整概述，这是我推荐的一个很好的资源：https ://excelmacromastery.com/vba-dictionary/

Public Sub CountComponent()
Application.ScreenUpdating = False
Sheets("Calculator").Unprotect Password:="secret"
Set wsComponentData = Sheets("Components Data")
Set wsCalculator = Sheets("Calculator")

'//Get the index of the last filled row based on column A
LastComponentRowIndex = wsComponentData.Cells(Rows.Count, "A").End(xlUp).Row

'//Get Range for ComponentData
Set ComponentRange = wsComponentData.Range("A2:A" & LastComponentRowIndex)

'//Get the index of the last filled row based on column C
LasttotalauditRowIndex = wsCalculator.Cells(Rows.Count, "C").End(xlUp).Row

'//Get range for Calculator
Set MyRange = wsCalculator.Range("C2:C" & LasttotalauditRowIndex)
TotalCalls = WorksheetFunction.Sum(MyRange)

'// Declare a new dictionary
dim componentDict as New Scripting.Dictionary

'// First loop through the Calculator sheet to get each component 
'// and set initial value to zero
dim i as Long, lastCalcColumn as Long
lastCalcColumn = wsCalculator.Cells(1, Columns.count).end(xlToLeft).Column

for i = 4 to lastCalcColumn
    '// Adding each item to dictionary, a couple of ways to write this,
    '// but this is probably the easiest
    componentDict(wsCalculator.Cells(i, 1).Value) = 0
next i

'//Looping through all filled rows in the Components Data sheet
'// I changed this to loop through each row in your component sheet
'// So that we can accumulate the total occurences
dim current_key as String

For i = 2 To LastComponentRowIndex
    If wsComponentData.Range("G" & i).Value <> "" Then
        '// assuming component names are in the "G" column
        '// change this as needed
        current_key = wsComponentData.Range("G" & i).Value
        componentDict(current_key) = componentDict(current_key) + 1  
    end if
Next i

'// now back to the Calculator sheet to enter the values
for i = 4 to lastCalcColumn
    current_key = wsCalculator.Cells(i, 1).Value
    wsCalculator.Cells(i, 2).Value = componentDict(current_key)
next i

End Sub

推荐阅读