时间戳

首页 > 解决方案 > Excel 将循环结果输出到带有循环的每一行的新单元格

excel - Excel 将循环结果输出到带有循环的每一行的新单元格

问题描述

我是 Excel 宏的新手，我正在尝试使用 Countif 函数来确定“字符串”在数组 (c1:I100) 中出现的次数。然后将此结果作为整数输出到单独工作表上的单元格中。我想在一个循环中计算 7 个不同的“字符串”，并输出到与每个新“字符串”的第一个输出相同的列中的一个新单元格。这就是我所拥有的，它在用第一个计数的字符串值填充所有输出单元格后导致 excel 冻结。

Sub LayerIssue_CountIf()
Dim SubRange As Range
Dim Output1, Output2, Output3, Output4, Output5, Output6, Output7, Output8 As Range
Dim Lookupname As String

Set SubRange = Range("C1:I100")
Set Output1 = Sheet4.Range("C2")
Set Output2 = Sheet4.Range("C3")
Set Output3 = Sheet4.Range("C4")
Set Output4 = Sheet4.Range("C5")
Set Output5 = Sheet4.Range("C6")
Set Output6 = Sheet4.Range("C7")
Set Output7 = Sheet4.Range("C8")
Set Output8 = Sheet4.Range("C9")

y = 2

Do While y = 2
  If Cells(y, 3) = "" Then
  Lookupname = "Jans .5"
  Output1 = WorksheetFunction.CountIf(SubRange, Lookupname)
  
  ElseIf y = 3 Then
  Lookupname = "Jans .4"
  Output2 = WorksheetFunction.CountIf(SubRange, Lookupname)
  
  ElseIf y = 4 Then
  Lookupname = "Jans .3"
  Output3 = WorksheetFunction.CountIf(SubRange, Lookupname)
  
  ElseIf y = 5 Then
  Lookupname = "Jans .2"
  Output4 = WorksheetFunction.CountIf(SubRange, Lookupname)
  
  ElseIf y = 6 Then
  Lookupname = "Jans .1"
  Output5 = WorksheetFunction.CountIf(SubRange, Lookupname)
  
  ElseIf y = 7 Then
  Lookupname = "Jans .05"
  Output6 = WorksheetFunction.CountIf(SubRange, Lookupname)
  
  ElseIf y = 8 Then
  Lookupname = "Jans .01"
  Output7 = WorksheetFunction.CountIf(SubRange, Lookupname)
End If
Loop
End Sub

标签： excelvbanew-operator

解决方案

在 VBA 编程中使用的 IF 并不多。Select Case将是首选武器。但是如果可以在没有条件的情况下确定结果，效率会更高。此外，请考虑使用数组而不是多个变量。

您的代码并不完全清楚您要对输出做什么。但是，下面的过程确实显示了您应该采取的方法。

Option Explicit

Sub LayerIssue_CountIf()

    Dim SubRange        As Range
    Dim Output(1 To 7)  As Long
    Dim LookupName()    As Variant
    Dim i               As Long                 ' loop counter: Output index
    
    ' The array will be 0-based. To match Output() 0 is added and left unused.
    LookupName = Array(0, 0.5, 0.4, 0.3, 0.2, 0.1, 0.05, 0.01)
    Set SubRange = Range("C2:I100")
    
    ' count from 1 to 7
    For i = LBound(Output) To UBound(Output)
        Output(i) = WorksheetFunction.CountIf(SubRange, "Jans " & Format(LookupName(i), ".0#"))
    Next i
    
    ' cell C3 is included in SubRange. Not sure you want to write there.
    With Cells(2, "B")
        If .Value = "" Then
            .Resize(UBound(Output), 1).Value = Application.Transpose(Output)
        End If
    End With
End Sub

推荐阅读