VBA异常具有“小于”的值。操作者 [英] VBA anomaly with the "less than" operator

问题描述

我正在尝试关联两个相当大的表，这些表有一个公共列来关联这两个表（称为"sitename"）。 Sitename是一个文本字符串，可能包含一些非字母数字。

我没有进行大量的vlookup类型操作，而是在sitename上对表进行排序，并在每个表中维护一个索引来控制迭代。这样我可以循环下来的行，并且随着网站名称的变化，控制哪些索引增加到
关联信息（两张表有多对一的关系）。

我遇到的问题是我的宏中的Range.sort方法产生的结果不一致，而不是"<"比较网站名称变量时的运算符。

特别是，例如，我设置了一些测试代码来演示问题：

Public Sub testSort （）$
Dim myRange作为范围

设置myRange =工作表（"Sheet1"）。范围（"a1"）。CurrentRegion

myRange.Sort Key1：= myRange.Cells（1,1），order1：= xlAscending，Header：= xlYes

Dim i As Integer

for i = 3 to myRange .Rows.Count

   如果myRange.Cells（i，1）.Value< myRange.Cells（i - 1,1）.Value然后是
        myRange.Cells（i，2）.Value =" true"

   否则

        myRange.Cells（i，2）.Value =" false"

   结束如果

下一页i

结束子

Sheet1包含按升序预先排序的数据表：

< tr>

sitename

CSGBSC429：S1：

CSGBSC429 ：S11：

CSGBSC429：S19：

CSGBSC429：S2：

CSGBSC429：S20：

但是，当我运行宏时，我得到以下内容： / p>

sitename	A< B？
CSGBSC429：S1：
CSGBSC429：S11：	TRUE
CSGBSC429：S19：	FALSE
CSGBSC429：S2：	FALSE
CSGBSC429：S20：	TRUE

当然第二列应该全部读为"FALSE"？比"或小于"或"小于" Excel排序函数中使用的运算符与VBA中用作小于运算符的运算符不同....

我对此行为感到有些困惑。

我不能做的事情：删除特殊字符 - 我不能保证这不会产生重复的地方。同样用可能解决这一特定难题的正确字母数字替换特殊字符，但
再次不能保证不会产生错误重复。创建哈希表似乎重新创建了"执行大量查找"。问题因为我有O（10K）条目要处理。

解决方案

尝试使用工作表函数：

Public Sub testSort2（）

Dim myRange As Range

设置myRange =工作表（" Sheet1"）。范围（" a1"）。CurrentRegion

myRange.Sort Key1：= myRange.Cells（1,1），order1：= xlAscending，Header：= xlYes

使用单元格（3,2）.Resize （myRange.Rows.Count - 2）

    .FormulaR1C1 =" = RC [-1]> R [-1] C [-1]"
$
    .Value = .Value

结束

End Sub

I'm trying to correlate two rather large tables that have a common column to relate the two (called "sitename"). Sitename is a text string that might contain some non-alphanumerics.

Rather than doing an awful lot of vlookup type operations, I sort the tables on sitename and maintain an index in each table to control the iterations. That way I can loop down the rows and, as the sitename changes, control which indexes to increment to correlate info (the two sheets have a many-to-one relationship).

The problem I have is that the Range.sort method in my macro produces inconsistent results than with the "<" operator when comparing the sitename variables.

In particular, for example, I setup a bit of test code to demonstrate the problem:

Public Sub testSort()
Dim myRange As Range
Set myRange = Worksheets("Sheet1").Range("a1").CurrentRegion
myRange.Sort Key1:=myRange.Cells(1, 1), order1:=xlAscending, Header:=xlYes
Dim i As Integer
For i = 3 To myRange.Rows.Count
    If myRange.Cells(i, 1).Value < myRange.Cells(i - 1, 1).Value Then
        myRange.Cells(i, 2).Value = "true"
    Else
        myRange.Cells(i, 2).Value = "false"
    End If
Next i
End Sub

Sheet1 contains a table of data that is pre-sorted in ascending order:

sitename

CSGBSC429:S1:

CSGBSC429:S11:

CSGBSC429:S19:

CSGBSC429:S2:

CSGBSC429:S20:

When I run the macro, though, I get the following:

sitename	A<B?
CSGBSC429:S1:
CSGBSC429:S11:	TRUE
CSGBSC429:S19:	FALSE
CSGBSC429:S2:	FALSE
CSGBSC429:S20:	TRUE

Surely the second columns should all read "FALSE"? Either than, or the "less than" operator used in the Excel sort function is different to that used as the less than operator in VBA....

I'm somewhat puzzled by this behaviour.

Things I can't do: strip out the special characters - I can't guarantee that this will not produce duplicates where there aren't already. Ditto for replacing the special characters with a proper alphanumeric that might solve this particular conundrum, but again won't guarantee not producing false-duplicates. Creating a hash-table seems like re-creating the "doing lots of lookups" issue as I have O(10K) entries to process.

解决方案

Try using worksheet functions instead:

Public Sub testSort2()
Dim myRange As Range
Set myRange = Worksheets("Sheet1").Range("a1").CurrentRegion
myRange.Sort Key1:=myRange.Cells(1, 1), order1:=xlAscending, Header:=xlYes

With Cells(3, 2).Resize(myRange.Rows.Count - 2)
    .FormulaR1C1 = "=RC[-1]>R[-1]C[-1]"
    .Value = .Value
End With

End Sub

这篇关于VBA异常具有“小于”的值。操作者的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！