Excel VBA 如果 cell.Value =... 那么

Excel VBA If cell.Value =... then

提问by Kerem Ozan Bayraktar

I have this code which works well:

我有这个运行良好的代码：

Sub colortest()
Dim cell As Range
For Each cell In Range("Range1")
If cell.Value = "Word1" Then
cell.Interior.Color = XlRgbColor.rgbLightGreen
ElseIf cell.Value = "Word2" Then
cell.Interior.Color = XlRgbColor.rgbOrange
ElseIf cell.Value = "Word3" Then
cell.Interior.Color = XlRgbColor.rgbRed
End If
Next cell
End Sub

My problem is the values work only if the cell contains only that text. But my cells are usually like this: "Something, 10254, 15/15, Word1Another Word" I only need the Word1. Thank you,

我的问题是这些值仅在单元格仅包含该文本时才起作用。但是我的单元格通常是这样的：“Something, 10254, 15/15, Word1Another Word” 我只需要Word1。谢谢，

采纳答案by Netloh

You can determine if as certain word is found in a cell by using

您可以通过使用确定是否在单元格中找到某个单词

If InStr(cell.Value, "Word1") > 0 Then

If Word1 is found in the string the InStr()function will return the location of the first character of Word1 in the string.

如果在字符串中找到 Word1，该InStr()函数将返回字符串中 Word1 的第一个字符的位置。

回答by crazylane

I think it would make more sense to use "Find" function in Excel instead of For Each loop. It works much much faster and it's designed for such actions. Try this:

我认为在 Excel 中使用“查找”函数而不是 For Each 循环会更有意义。它的工作速度要快得多，并且专为此类操作而设计。尝试这个：

 Sub FindSomeCells(strSearchQuery As String)   

    Set SearchRange = Worksheets("Sheet1").Range("A1:A100")
    FindWhat = strSearchQuery
    Set FoundCells = FindAll(SearchRange:=SearchRange, _
                            FindWhat:=FindWhat, _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByColumns, _
                            MatchCase:=False, _
                            BeginsWith:=vbNullString, _
                            EndsWith:=vbNullString, _
                            BeginEndCompare:=vbTextCompare)
    If FoundCells Is Nothing Then
        Debug.Print "Value Not Found"
    Else
        For Each FoundCell In FoundCells
            FoundCell.Interior.Color = XlRgbColor.rgbLightGreen
        Next FoundCell
    End If

End Sub

That subroutine searches for some string and returns a collections of cells fullfilling your search criteria. Then you can do whatever you want with the cells in that collection. Forgot to add the FindAllfunction definition:

该子例程搜索某个字符串并返回满足搜索条件的单元格集合。然后您可以对该集合中的单元格做任何您想做的事情。忘记添加FindAll函数定义：

Function FindAll(SearchRange As Range, _
                FindWhat As Variant, _
               Optional LookIn As XlFindLookIn = xlValues, _
                Optional LookAt As XlLookAt = xlWhole, _
                Optional SearchOrder As XlSearchOrder = xlByRows, _
                Optional MatchCase As Boolean = False, _
                Optional BeginsWith As String = vbNullString, _
                Optional EndsWith As String = vbNullString, _
                Optional BeginEndCompare As VbCompareMethod = vbTextCompare) As Range
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' FindAll
' This searches the range specified by SearchRange and returns a Range object
' that contains all the cells in which FindWhat was found. The search parameters to
' this function have the same meaning and effect as they do with the
' Range.Find method. If the value was not found, the function return Nothing. If
' BeginsWith is not an empty string, only those cells that begin with BeginWith
' are included in the result. If EndsWith is not an empty string, only those cells
' that end with EndsWith are included in the result. Note that if a cell contains
' a single word that matches either BeginsWith or EndsWith, it is included in the
' result.  If BeginsWith or EndsWith is not an empty string, the LookAt parameter
' is automatically changed to xlPart. The tests for BeginsWith and EndsWith may be
' case-sensitive by setting BeginEndCompare to vbBinaryCompare. For case-insensitive
' comparisons, set BeginEndCompare to vbTextCompare. If this parameter is omitted,
' it defaults to vbTextCompare. The comparisons for BeginsWith and EndsWith are
' in an OR relationship. That is, if both BeginsWith and EndsWith are provided,
' a match if found if the text begins with BeginsWith OR the text ends with EndsWith.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Dim FoundCell As Range
Dim FirstFound As Range
Dim LastCell As Range
Dim ResultRange As Range
Dim XLookAt As XlLookAt
Dim Include As Boolean
Dim CompMode As VbCompareMethod
Dim Area As Range
Dim MaxRow As Long
Dim MaxCol As Long
Dim BeginB As Boolean
Dim EndB As Boolean
CompMode = BeginEndCompare
If BeginsWith <> vbNullString Or EndsWith <> vbNullString Then
    XLookAt = xlPart
Else
    XLookAt = LookAt
End If
' this loop in Areas is to find the last cell
' of all the areas. That is, the cell whose row
' and column are greater than or equal to any cell
' in any Area.

For Each Area In SearchRange.Areas
    With Area
        If .Cells(.Cells.Count).Row > MaxRow Then
            MaxRow = .Cells(.Cells.Count).Row
        End If
        If .Cells(.Cells.Count).Column > MaxCol Then
            MaxCol = .Cells(.Cells.Count).Column
        End If
    End With
Next Area
Set LastCell = SearchRange.Worksheet.Cells(MaxRow, MaxCol)
On Error GoTo 0
Set FoundCell = SearchRange.Find(what:=FindWhat, _
        after:=LastCell, _
        LookIn:=LookIn, _
        LookAt:=XLookAt, _
        SearchOrder:=SearchOrder, _
        MatchCase:=MatchCase)
If Not FoundCell Is Nothing Then
    Set FirstFound = FoundCell
    Do Until False ' Loop forever. We'll "Exit Do" when necessary.
        Include = False
        If BeginsWith = vbNullString And EndsWith = vbNullString Then
            Include = True
        Else
            If BeginsWith <> vbNullString Then
                If StrComp(Left(FoundCell.Text, Len(BeginsWith)), BeginsWith, BeginEndCompare) = 0 Then
                    Include = True
                End If
            End If
            If EndsWith <> vbNullString Then
                If StrComp(Right(FoundCell.Text, Len(EndsWith)), EndsWith, BeginEndCompare) = 0 Then
                    Include = True
                End If
            End If
        End If
        If Include = True Then
            If ResultRange Is Nothing Then
                Set ResultRange = FoundCell
            Else
                Set ResultRange = Application.Union(ResultRange, FoundCell)
            End If
        End If
        Set FoundCell = SearchRange.FindNext(after:=FoundCell)
        If (FoundCell Is Nothing) Then
            Exit Do
        End If
        If (FoundCell.Address = FirstFound.Address) Then
            Exit Do
        End If
    Loop
End If
Set FindAll = ResultRange
End Function

回答by Aiken

You can use the Likeoperator with a wildcard to determine whether a given substring exists in a string, for example:

您可以使用Like带有通配符的运算符来确定给定的子字符串是否存在于字符串中，例如：

If cell.Value Like "*Word1*" Then
'...
ElseIf cell.Value Like "*Word2*" Then
'...
End If

In this example the *character in "*Word1*"is a wildcard character which matches zero or more characters.

在此示例中，*字符 in"*Word1*"是匹配零个或多个字符的通配符。

NOTE:The Likeoperator is case-sensitive, so "Word1" Like "word1"is false, more information can be found on this MSDN page.

注：该Like操作是大小写敏感的，所以"Word1" Like "word1"是假的，更多的信息可以发现这个MSDN网页上。