VBA中的高级筛选条件问题（Excel 2007） [英] Problems with Advanced Filter Criteria in VBA (Excel 2007)

问题描述

我在Excel 2007中创建的工作表上运行高级过滤器时出现问题。

问题是如果我尝试使用用户定义的函数生成条件，则过滤器失败（即，即使存在过滤器应该拾取的结果，也不会返回结果）。 UDF在适当时返回一个vbNullString，否则返回一个
的简单文本字符串。

然后我尝试在虚拟单元格中生成条件（使用相同的UDF）并执行复制和粘贴特殊，只将值复制到条件单元格中。过滤器仍然失败并且没有返回结果。虚拟单元格和
条件单元格中的所有内容都看起来没有 - 没有不需要的条目，没有隐藏的零等。

如果我清除所有条件单元格然后手动输入完全相同（非零）标准进入相关单元格，然后过滤器工作，并返回正确数量的答案。

问题似乎在处理外卡（我认为！）我有很多标准单元格，只有少数单元显示值。空单元格"看起来"空的，当它们是空的时，我希望过滤器跳过该特定标准 - 在其他
单词中，以处理空白单元格，如"*"字符。当我粘贴特殊内容时，我正在跳过空白，在复制和粘贴之前，我清除了标准范围的内容，以确保没有任何令人讨厌的问题。

标准必须动态更改，所以每次输入都不可行。我在虚拟单元格和标准单元格中使用通用数字格式。关于我可能做错了什么和/或如何解决这个问题，请问任何想法？

非常感谢

Darren

PS请放轻松 - 我不是VB专家： - ）

解决方案

你好，再次，

我已经做了一些进一步的调查，我认为这个问题与野外卡没有关系，而是与我在之前的帖子中提到的UDF有关。我使用下面的UDF（PREPSEARCH）处理一系列下拉菜单，文本框和另一张表上的
框的输出。 PREPSEARCH然后填充一个虚拟范围，我将其复制并粘贴到标准范围内（值和数字格式）。也许如果我解决了这个问题，那么我可以直接在条件单元格中使用PREPSEARCH，但是对于
，我现在必须使用复制和粘贴特殊替代方案。

我刚刚发现，即使输出看起来与我相同，通过PREPSEARCH生成的复制到条件范围内的任何条件都不起作用，但是当我键入
完全手动相同的标准进入标准范围，他们这样做。

所以我认为必须是PREPSEARCH，这是责任，不是高级过滤器。 关于我做错了什么想法？

非常感谢

Darren

公共函数PREPSEARCH（作为范围的主题，可选标记为整数）
'将报表页面的数据输出转换为适合用作搜索Citeria的格式
 
'如果未设置Flag，则Subject指的是CheckBox; 
'如果Flag为1，则Subject为文本输入框或下拉菜单; 
 
'需要为具有Flag = 1 
 
'的主题分配逻辑运算符如果Flag设置为1，则函数将主题值零视为空字符串（ """或通配符，而不是
'，而不是数值为零。对于未设置的Flag主题，则将其视为零值。 
 
 Application.Volatile 
 
 Dim LogOpr As String，TempRng As Range，TempStr As String 
 
 Set TempRng = Application.ThisCell 
 
 Select Case Subject.Value 
 
 Case 0 
 If Flag = 1 Then 
 PREPSEARCH = vbNullString 
 Else 
 PREPSEARCH = 0 
结束如果
 
案例"ANY" 
 PREPSEARCH = vbNullString 
 
 Case Else 
 
如果Flag = 1则'Subject与非空文本字段或下拉值不同于' ; ANY" 
 
'获取主题的逻辑运算符 - 这将存储在Sheet上的B列（第2列）中，其中主题定义为
 LogOpr = Sheets（Subject.Parent.Name）。单元格（Subject.Row，2）.Value 
 
如果LogOpr =" ="然后
 PREPSEARCH = Subject.Value 
否则
 PREPSEARCH = LogOpr& Subject.Value 
结束如果
 
否则'主题与CheckBox有关，因此不需要逻辑运算符
 
 PREPSEARCH = Subject.Value 
 
结束如果
 
结束选择
 
标志= 0 
 
结束功能

Hi,

I'm having problems running an advanced filter on a worksheet created in Excel 2007.

The problem is that if I attempt to generate the criteria using a User-Defined Function, the filter fails (i.e. returns no results, even though there are results that the filter should be picking up). The UDF returns a vbNullString when appropriate and a simple text string otherwise.

Then I tried generating the criteria in dummy cells (using the same UDF) and doing a Copy and Paste special to copy just the values into the criteria cells. Still the filter fails and returns no results. Everything looks OK in both the dummy cells and the criteria cells - there are no unwanted entries, no hidden zeros etc.

If I clear all the criteria cells and then manually type exactly the same (non-zero) criteria into the relevant cells, then the filter works, and returns the right number of answers.

The problem seems to be in handling wild cards (I think!) I've got a lot of criteria cells, with only a few of them showing values. The empty cells "look" empty, and when they are empty, I want the filter to skip that particular criteria - in other words, to treat blank cells like a "*" character. When I paste special, I'm skipping blanks and before copying and pasting, I clear the contents of the criteria range to make sure there's nothing nasty left lying around.

The criteria must change dynamically, so it's not feasible to type them in each time. I'm using General number format in both the dummy cells and the criteria cells. Any ideas, please, as to what I might be doing wrong and/or how to get round this?

With many thanks

Darren

PS Please go easy on me - I'm not a VB expert :-)

解决方案

Hello again,

I've done some further investigation and I think the problem is not to do with the wild cards, it is to do with the UDF I referred to in my earlier post. I use the UDF below (PREPSEARCH) to handle the output from a series of drop downs, text boxes and tick boxes on an another sheet. PREPSEARCH then populates a dummy range that I copy and paste special (values and number formats) into the criteria range. Perhaps if I get this problem sorted out, then I can use PREPSEARCH directly in the criteria cells, but for now I'm having to use the Copy and Paste special alternative.

I've just discovered that even though the output looks identical to me, any criteria generated via PREPSEARCH that are copied into the criteria range don't work, but when I type exactly the same criteria into the criteria range manually, they do.

So I reckon it must be PREPSEARCH that is to blame, not the Advanced Filter.  Any ideas, please, as to what I'm doing wrong??

Many thanks

Darren

Public Function PREPSEARCH(Subject As Range, Optional Flag As Integer)
'Converts data output from the Report Page into format suitable for use as a Search Citeria

' If Flag is not set, then Subject refers to a CheckBox;
' If Flag is 1, then Subject is a Text entry box or a drop down menu;

' Need to assign a logical operator to subjects with Flag = 1

' If Flag is set to 1 then then function treats a Subject value of zero as being the nullstring ("") or wildcard, rather
' than being a numerical value of zero. For an unset Flag Subject, then it is treated as a value of zero.

Application.Volatile

Dim LogOpr As String, TempRng As Range, TempStr As String

Set TempRng = Application.ThisCell

Select Case Subject.Value

Case 0
  If Flag = 1 Then
    PREPSEARCH = vbNullString
  Else
    PREPSEARCH = 0
  End If

Case "ANY"
  PREPSEARCH = vbNullString

Case Else

If Flag = 1 Then 'Subject relates to a non-empty text field or a drop down with a value other than "ANY"

  ' Get the logical operator for the subject - this is stored in column B (column #2) on the Sheet where subject is defined
  LogOpr = Sheets(Subject.Parent.Name).Cells(Subject.Row, 2).Value

    If LogOpr = "=" Then
      PREPSEARCH = Subject.Value
    Else
      PREPSEARCH = LogOpr & Subject.Value
    End If

Else 'Subject relates to a CheckBox, so no Logical Operator is required

  PREPSEARCH = Subject.Value

End If

End Select

Flag = 0

End Function

这篇关于VBA中的高级筛选条件问题（Excel 2007）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！