需要帮助来建立宏 [英] Need help to build a macro

问题描述

我在excel 2007中需要一个宏，该宏可以过滤并从一系列列中删除不需要的数据，并且最后只保留所需的数据.

I need a macro in excel 2007 which can filter & remove unwanted data from a range of columns and only the desired data is delievered at the end.

推荐答案

我认为他正在寻找SUMIF或COUNTIF之类的VBA函数，但是返回整个输入范围，其中某些单元格被清空(从而根据条件过滤输入值).您可以使用内置函数使用数组公式执行此操作.

假设您的输入范围是A1:C10，请高亮显示D1:F10，输入= IF(A1:C10> 5，A1:C10，")，然后按Control-Shift-Enter键而不是直接输入.这样就可以得到一个数组公式，其输出范围与输入范围相同.

现在，假设这是您要执行的操作，但需要一个宏来执行此操作，以便可以执行更智能的操作，则可以使用以下命令:

假设您的输入范围是A1:C10，则要使用此宏突出显示D1:F10，请输入= FilterMacro(A1:C10，>"，5，")，然后按control-shift-enter而不是直接输入.这样就可以得到一个数组公式，其输出范围与输入范围相同.

I think he is looking for a VBA function like SUMIF or COUNTIF but returning the entire input range with some cells blanked out (thereby filtering the input values based on a condition) You can do this with an array formula using built in functions.

Assuming your input range is A1:C10, highlight D1:F10, enter =IF(A1:C10>5,A1:C10,"") and press control-shift-enter instead of just enter. That gets you an array formula with the same size output range as the input range.

Now, assuming that is the kind of thing you want to do except you want a macro that does it so you can do something more intelligent, you can use the following:

Assuming your input range is A1:C10, then to use this macro highlight D1:F10, enter =FilterMacro(A1:C10,">",5,"") and press control-shift-enter instead of just enter. That gets you an array formula with the same size output range as the input range.

Public Function FilterMacro(ByRef InputValues As Range, Operation As String, ConditionValue As Variant, EmptyValue As Variant) As Variant

    Dim Result As Variant
    Dim MatchesFilter As Boolean

    ' store all the input values
    Result = InputValues.Value2

    For CurrentRow = 1 To InputValues.Rows.Count
        For CurrentColumn = 1 To InputValues.Columns.Count
            Select Case Operation
            Case ">":  MatchesFilter = InputValues(CurrentRow, CurrentColumn) > ConditionValue
            Case "<":  MatchesFilter = InputValues(CurrentRow, CurrentColumn) < ConditionValue
            Case "<>": MatchesFilter = InputValues(CurrentRow, CurrentColumn) <> ConditionValue
            Case "=":  MatchesFilter = InputValues(CurrentRow, CurrentColumn) = ConditionValue
            End Select
            If Not MatchesFilter Then
                ' clear the ones we don't want
                Result(CurrentRow, CurrentColumn) = EmptyValue
            End If
        Next
    Next
    FilterMacro = Result
End Function

抱歉，它不能很好地显示在屏幕上.也许我应该使用较短的变量名...

Sorry it doesn''t fit on the screen so well. Perhaps I should have used shorter variable names...

这篇关于需要帮助来建立宏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！