忽略单元格中的字母，只能检查数字 [英] Ignore alphabets in a cell, only numbers should be checked

问题描述

 Sub highlight(phm as variant)
 Dim w As Workbook
 Dim sh As Worksheet
 Dim x As Integer
 Dim rn As Range
 Dim k As Long
 Dim 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("Number Type").Select

Set rn = sh6.UsedRange
k = rn.Rows.Count + rn.Row - 1
On Error Resume Next
For i = 1 To k
For j = LBound(number) To UBound(number)
number(j) = CInt(phm(j))
If Err.number = 0 Then
If ActiveCell.Value = number(j) Or IsEmpty(ActiveCell.Value) Then
Selection.Interior.ColorIndex = xlNone
Else
Selection.Interior.Color = vbGreen
Exit For
End If
End If

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


End If
ActiveWorkbook.Save


End Sub

我想修改上述代码，使得如果存在于任何单元格中，字母将被忽略。

I want to modify the above code in such a way that alphabets are ignored if present in any cell.

例如，单元格可能包含hello 9811，那么它不应该被突出显示。只能对单元格中的数字进行检查

For example, a cell may contain "hello 9811",then it should not be highlighted.Checking should be done only on numbers in the cell

phm包含这样的数据：9811,7849等。

phm contains data like this: "9811,7849" etc..

推荐答案

这是您的程序的修改版本。程序尝试将单元格的值转换为整数。只有成功这样做，那么 Activecell.Value 与 number（j）进行比较。

Here's a modified version of your program. The program tries to convert the cell's value to an integer. Only if it succeeds in doing so, then Activecell.Value is compared to number(j).

Sub Highlight()

    ...same code as yours...

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

    ' ignore errors related to CInt conversion that will follow
    On Error Resume Next

    For x = 1 To k
      For j = 0 To UBound(number)

        ' try to convert value to integer
        TempNumber = CInt(ActiveCell.Value)

        ' if value was an integer, work on it
        If Err.number = 0 Then
            If ActiveCell.Value <> number(j) Then
               Selection.Interior.Color = vbYellow
            Else
               Selection.Interior.ColorIndex = xlNone
               Exit For
            End If
        End If

      Next j

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

    Next x

End Sub

根据需求变化编辑

Sub Test()
    highlight ("9811,7849")
End Sub

Sub highlight(phm As Variant)

    Dim w As Workbook
    Dim sh As Worksheet
    Dim x As Integer
    Dim rn As Range
    Dim k As Long
    Dim number() As Integer

    ' newly added variables
    Dim TempNumber As Integer
    Dim phmInt As Variant
    Dim phmFound As Boolean

    If phm <> 0 Then

        ' split the numbers
        phm = Split(phm, ",")
        ReDim number(LBound(phm) To UBound(phm)) As Integer

        Set sh = Worksheets("sheet1")
        sh.Select
        Cells.Find("Number Type").Select

        Set rn = sh.UsedRange
        k = rn.Rows.Count + rn.Row - 1

        For i = 1 To k

            On Error Resume Next

            ' try to check if active cell is an integer
            ' and proceed only if it is an integer
            TempNumber = CInt(ActiveCell.Value)
            If Err.number = 0 Then
                On Error GoTo 0

                ' set phmFound to false and then see if
                ' active cell's value matches any item in phm array
                phmFound = False
                For Each phmInt In phm
                    If CInt(ActiveCell.Value) = CInt(phmInt) Then
                        phmFound = True
                        Exit For
                    End If
                Next phmInt

                ' if active cell's value matched at least one item
                ' in phm array, don't colorize it. Otherwise colorize it
                ActiveCell.Select
                If phmFound Then
                    Selection.Interior.ColorIndex = xlNone
                Else
                    Selection.Interior.Color = vbGreen
                End If

            End If
            Err.Clear

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

    End If

End Sub

编辑

要求：输入9811和7848，以此格式的任何单元格 - hello 9811,9811,7848，abc 7848不应该被突出显示，除了上述以外的任何其他内容的剩余单元格应该被突出显示

Requirement: 9811 and 7848 are entered so any cell in this format- hello 9811,9811,7848,abc 7848 should NOT be highlighted...remaining cells with any other content other than the above mentioned should be highlighted

Sub Test（）
highlight（9811， 7848）
End Sub

Sub Test() highlight ("9811,7848") End Sub

Sub highlight(phm As Variant)

    Dim w As Workbook
    Dim sh As Worksheet
    Dim x As Integer
    Dim rn As Range
    Dim k As Long
    Dim number() As Integer

    ' newly added variables
    Dim TempNumber As Integer
    Dim phmInt As Variant
    Dim phmFound As Boolean

    If phm <> 0 Then

        ' split the numbers
        phm = Split(phm, ",")
        ReDim number(LBound(phm) To UBound(phm)) As Integer

        Set sh = Worksheets("sheet1")
        sh.Select
        Cells.Find("Number Type").Select

        Set rn = sh.UsedRange
        k = rn.Rows.Count + rn.Row - 1

        For i = 1 To k

            ' does the cell have the number we are looking for?
            phmFound = False
            For Each phmInt In phm
                TempNumber = InStr(Trim(ActiveCell.Text), CStr(phmInt))
                If TempNumber > 0 Then
                    ' check if there is any number after phmint
                    If Not IsNumeric(Mid(Trim(ActiveCell.Text), TempNumber + Len(CStr(phmInt)), 1)) Then
                        phmFound = True
                        Exit For
                    End If
                End If
            Next phmInt

            ' if active cell's value matched at least one item
            ' in phm array, don't colorize it. Otherwise colorize it
            ActiveCell.Select
            If phmFound Then
                Selection.Interior.ColorIndex = xlNone
            Else
                Selection.Interior.Color = vbGreen
            End If

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

    End If

End Sub

这篇关于忽略单元格中的字母，只能检查数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！