Results 1 to 9 of 9

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

Threaded View

  1. #1
    Registered User
    Join Date
    03-24-2012
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    6

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

    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
    Last edited by waf04; 03-24-2012 at 08:15 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1