VBA Excel, scan range of cells for a keyword, then fill in another cell with an x if found

**waf04** · 03-24-2012, 08:08 PM

Hi, I am very new to VBA, and I am trying to do the following, I appreciate any help you may provide.

Scan range at2: ep200 for the word salt, if the word is found, then on that same row that the word is found, place an x under column M (of the same row where it was found). Also I imagine I can tie this to a list of words not just one?

Below is some sample code I was working on, it is probably more complicated than it needs to be.

This module scans cells for words to fill in with x in other cells

=====================================================================
Option Explicit

Function CountOccurrences(strText As String, strFind As String, Optional lngCompare As VbCompareMethod) As Long

' Count occurrences of a particular character or characters.
' If lngCompare argument is omitted, procedure performs binary comparison.
Dim lngPos As Long
Dim lngTemp As Long
Dim lngCount As Long

' Specify a starting position. We don't need it the first
' time through the loop, but we'll need it on subsequent passes.
lngPos = 1

' Execute the loop at least once.
Do

' Store position at which strFind first occurs.
lngPos = InStr(lngPos, strText, strFind, lngCompare)

' Store position in a temporary variable.
lngTemp = lngPos

' Check that strFind has been found.
If lngPos > 0 Then

' Increment counter variable.
lngCount = lngCount + 1

' Define a new starting position.
lngPos = lngPos + Len(strFind)
End If

' Loop until last occurrence has been found.
Loop Until lngPos = 0

' Return the number of occurrences found.
CountOccurrences = lngCount
End Function

Sub CheckLowSodium()
'
' Keyboard Shortcut: Option+Cmd+z

' This program looks through each cell in the Range (AT2:EP200) , and finds
' out if it has the word salt, and if so highlights column M

'Variables declaration
Dim Salt As Boolean
Dim Cell As Range

' In a range, perform function CountOccurrences to find a comma
' set Salt equal to true if found
For Each Cell In Range("AT2", "EP200")
Salt = CountOccurrences(Cell.Value, "salt", vbTextCompare)

'I believe this is where I need to structure it to check if cell is empty

' If salt found, then in the same row, mark X under column X
If Salt Then
' Fill in the same row under column M with the letter x
End If

' Go to next cell in range (AT2:EP200)
Next Cell

End Sub

**AlphaFrog** · 03-24-2012, 11:17 PM

Sub CheckLowSodium()
'
' Keyboard Shortcut: Option+Cmd+z
'
' This program looks through each cell in the Range (AT2:EP200) , and finds
' out if it has the word salt, and if so highlights column M

    Dim rngSearch As Range
    Dim vList As Variant, vWord As Variant
    Dim Found As Range, FirstFound As String
    Dim Counter As Long
    
    Set rngSearch = Range("AT2:EP200")      'Search range
    
    vList = Array("Salt")   'One word list
    'vList = Array("Salt", "Pepper", "Cumin", "Oregano")    'Example list of search words
    
    For Each vWord In vList 'Loop through each search word in list
    
        Set Found = Nothing
        
        'Find first occurrence of word
        Set Found = rngSearch.Find(What:=vWord, _
                                   LookIn:=xlValues, _
                                   LookAt:=xlPart, _
                                   MatchCase:=False)
                                            
        If Not Found Is Nothing Then    'If a match was found...
            FirstFound = Found.Address  'Store address of first occurrence to stop loop
            Do
                Range("M" & Found.Row).Value = "x"              'Tag column M with x
                Counter = Counter + 1                           'Count found words
                Set Found = rngSearch.FindNext(After:=Found)    'Find next occurrence
            Loop Until Found.Address = FirstFound               'Loop until the next found occurrence is the first occurrence
        End If
    
    Next vWord  ' next word in list
    
    'Display message
    MsgBox Counter & " words found and tagged in column M. ", , "Rows Tagged Commplete"

End Sub

**waf04** · 03-25-2012, 12:00 PM

WOW, thanks, I will mess around with this. You guys are geniuses!

Originally Posted by AlphaFrog

Sub CheckLowSodium()
'
' Keyboard Shortcut: Option+Cmd+z
'
' This program looks through each cell in the Range (AT2:EP200) , and finds
' out if it has the word salt, and if so highlights column M

    Dim rngSearch As Range
    Dim vList As Variant, vWord As Variant
    Dim Found As Range, FirstFound As String
    Dim Counter As Long
    
    Set rngSearch = Range("AT2:EP200")      'Search range
    
    vList = Array("Salt")   'One word list
    'vList = Array("Salt", "Pepper", "Cumin", "Oregano")    'Example list of search words
    
    For Each vWord In vList 'Loop through each search word in list
    
        Set Found = Nothing
        
        'Find first occurrence of word
        Set Found = rngSearch.Find(What:=vWord, _
                                   LookIn:=xlValues, _
                                   LookAt:=xlPart, _
                                   MatchCase:=False)
                                            
        If Not Found Is Nothing Then    'If a match was found...
            FirstFound = Found.Address  'Store address of first occurrence to stop loop
            Do
                Range("M" & Found.Row).Value = "x"              'Tag column M with x
                Counter = Counter + 1                           'Count found words
                Set Found = rngSearch.FindNext(After:=Found)    'Find next occurrence
            Loop Until Found.Address = FirstFound               'Loop until the next found occurrence is the first occurrence
        End If
    
    Next vWord  ' next word in list
    
    'Display message
    MsgBox Counter & " words found and tagged in column M. ", , "Rows Tagged Commplete"

End Sub

**waf04** · 03-25-2012, 12:04 PM

Just tried it, utterly amazing!! Thanks!

**waf04** · 03-25-2012, 01:10 PM

OK, what can I use to reference a list of words in a range of cells? Or a more efficient way than typing "salt", "xxx", "yyy"?

And where can I put a counter statement so as to change the code to mark x if these words are NOT found, currently it marks If Found?

Thank you for helping me get the hang of this

**AlphaFrog** · 03-25-2012, 11:27 PM

Originally Posted by waf04

OK, what can I use to reference a list of words in a range of cells? Or a more efficient way than typing "salt", "xxx", "yyy"?

What is the location of the range; Sheet name and Cells?

And where can I put a counter statement so as to change the code to mark x if these words are NOT found, currently it marks If Found?

You want to mark a row that you didn't find? I don't know how that works. Where does the "x" go?

VBA Excel, scan range of cells for a keyword, then fill in another cell with an x if found

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

VBA Excel, scan range of cells for a keyword, then fill in another cell with an x if found

Re: VBA Excel, scan range of cells for a keyword, then fill in another cell with an x if f

Re: VBA Excel, scan range of cells for a keyword, then fill in another cell with an x if f

Re: VBA Excel, scan range of cells for a keyword, then fill in another cell with an x if f

Re: VBA Excel, scan range of cells for a keyword, then fill in another cell with an x if f

Re: VBA Excel, scan range of cells for a keyword, then fill in another cell with an x if f

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions