☰

VBA 用于“IF 公式”，在单个单元格中有多个结果

tag-icon • tag-icon microsoft-excel vba macros
VBA 用于“IF 公式”，在单个单元格中有多个结果

我如何将其转换为 VBA？使用此公式，它仅覆盖 6 行，而且公式已经太长了。我的报告有 600 行。

=IF(AND('Risk & Issue Log'!$K$2=$D5,'Risk & Issue Log'!$L$2=F$8,OR('Risk & Issue Log'!$F$2="Open",'Risk & Issue Log'!$F$2="In-progress")),'Risk & Issue Log'!$D$2," ")&IF(AND('Risk & Issue Log'!$K$3=$D5,'Risk & Issue Log'!$L$3=F$8,OR('Risk & Issue Log'!$F$3="Open",'Risk & Issue Log'!$F$3="In-progress")),'Risk & Issue Log'!$D$3," ")&IF(AND('Risk & Issue Log'!$K$4=$D5,'Risk & Issue Log'!$L$4=F$8,OR('Risk & Issue Log'!$F$4="Open",'Risk & Issue Log'!$F$4="In-progress")),'Risk & Issue Log'!$D$4," ")&IF(AND('Risk & Issue Log'!$K$5=$D5,'Risk & Issue Log'!$L$5=F$8,OR('Risk & Issue Log'!$F$5="Open",'Risk & Issue Log'!$F$5="In-progress")),'Risk & Issue Log'!$D$5," ")&IF(AND('Risk & Issue Log'!$K$6=$D5,'Risk & Issue Log'!$L$6=F$8,'Risk & Issue Log'!$F$5="Open"),'Risk & Issue Log'!$D$6," ")

来自评论 -

我想要 -

  1. Risk & Issue Log将工作表上列中的每个值K与单元格进行比较D5
  2. Risk & Issue Log将工作表上列中的每个值L与单元格进行比较F8
  3. Risk & Issue Log将工作表上列中的每个值F与值进行比较Open或In-progress
  4. 对于每个row执行此操作的操作，D如果所有内容都匹配，则我想要相应列中单元格的结果，如果所有内容True都不blank匹配，则我想要相应列中单元格的结果。
  5. 我希望所有输出都在一个单元格中，并以逗号和空格分隔。

我该如何使用 VBA 来实现这一点？

答案1

在阅读完所有评论并最终弄清楚您要做什么（我认为）后...我相信以下 UDF 会起作用。将其粘贴到 VBA 中的新模块中，然后在要显示结果的任何单元格中输入 =summarizeLog() 并按回车键。

Function summarizeLog() As String

    'Define some variables
    Dim shtLog As Worksheet
    Dim shtSum As Worksheet
    Dim i As Integer

    'set the two worksheet variables
    Set shtLog = Sheets("Risk & Issue Log")
    Set shtSum = Sheets("Summary") '<---rename this to your needs


    'loop from 2 to 600 (these are the rows in your Risk & Issue Log to test, change to suit your needs)
    For i = 2 To 600

        'Now the test.
        'Does K2 through K600 equal D5, F2 through F600 equal either Open or In-Progress, and does L2 through L600 equal F8
        If shtLog.Range("K" & i).Value = shtSum.Range("D5") _
            And (shtLog.Range("F" & i).Value = "Open" Or shtLog.Range("F" & i).Value = "In-progress") _
            And shtLog.Range("L" & i).Value = shtSum.Range("F8") Then

            'If we passed, then grab the value in D for this row and append it the value that will be returned by this function
            '   (with a comma and space)
            summarizeLog = summarizeLog & ", " & shtLog.Range("D" & i).Value
        End If
    Next i
End Function

相关内容