在循环通过单元格时忽略字母 [英] ignore alphabets while looping through cells

问题描述

从我的GUI，我输入这样的数字：9811,7841将被发送到我的宏。我的宏是：

From my GUI , I enter numbers like this: 9811,7841 which will be sent to my macro. My macro is:

sub highlight(fm as variant)
dim sh as worksheet
Dim i As Integer
dim j as integer
dim k As Long
Dim rn As Range
din number() as integer
If phm <> 0 Then

phm = Split(phm, ",")
ReDim number(LBound(phm) To UBound(phm)) As Integer

Set sh = w.Worksheets("Sheet1")
sh.Select
Cells.Find("Type").Select
ActiveCell.Offset(1, 0).Select
 Set rn = sh.UsedRange
 k = rn.Rows.Count + rn.Row - 1

For i = 1 To k
For j = LBound(number) To UBound(number)

number(j) = CInt(phm(j))

If ActiveCell.Value = number(j) Or IsEmpty(ActiveCell.Value) Then
Selection.Interior.ColorIndex = xlNone
Else
Selection.Interior.Color = vbGreen
Exit For
End If


Next j
ActiveCell.Offset(1, 0).Select 'moves activecell down one row.
Next i


End If
ActiveWorkbook.Save


End Sub

我想修改我的代码，使得如果存在于任何单元格中，字母将被忽略。在下面的情况下，单元格3和单元格5应该是突出显示为我的fm包含9811,7841，所以单元格1,2,4是有效的。如果在检查单元格的时候，应该忽略字母。

I would like to modify my code in such that alphabets are ignored if present in any cell.In the below case, cell3 and cell 5 should be highlighted as my "fm" contains 9811,7841 so cell 1,2,4 are valid.Alphabets should be ignored if any while checking the cells.

Sheet1
cell 1: 9811
cell 2: hello 9811
cell 3: 3428
cell 4: hello 7841
cell 5:hello 2545

推荐答案

最简单的方式是使用正则表达式。添加对Microsoft VBScript正则表达式的引用，然后只做一个模式替换：

The simplest way to do this is with a regular expression. Add a reference to Microsoft VBScript Regular Expressions, then just do a pattern replacement:

Private Function StripNonNumerics(inValue As String) As String

    Dim regex As New RegExp
    With regex
        .Pattern = "\D"
        .Global = True
        StripNonNumerics = .Replace(inValue, vbNullString)
    End With

End Function

请注意那里如果您将此代码合并到子目录或使正则表达式成为全局（这样您就不必重复创建RegExp对象）就会减少开销。

Note that there'll be less overhead if you incorporate this into your sub or make the regex a global (that way you don't have to repeatedly create the RegExp object.

这篇关于在循环通过单元格时忽略字母的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！