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
Bookmarks