Help choosing cells with word within a word

[sweetness34]

Hi
I need some help with code to distinguish a range of cells that have a specific word. The problem is that word is within another word. For example I have a variation of the words "SandBox", "Box", and "Wood" all in one column. I need to find the cells that have the word "Box" and not "SandBox". Right now the code I have wont exclude "SandBox". This is what I have:

Dim x As String
Dim y As String
Dim z As String
Cells.Find(What:="Box", After:=Range("A1"), SearchDirection:=xlNext).Select
x = Selection.Offset(0, 4).Address
Cells.Find(What:="Box", After:=Range("A1"), SearchDirection:=xlPrevious).Select
y = Selection.Offset(0, 4).Address
z = Selection.Offset(1, 5).Address
ActiveWorkbook.Worksheets("Sheet1").Range(z).Select
Selection.Value = Application.WorksheetFunction.Sum(Range(x, y))

How can I modify this to only find "Box".
Thanks

 

[shg]

If that's all that's in the cell, use the LookAt argument (LookAt:=xlWhole)

 

[cgcamal]

Hi,

You can try something like:

Dim CurrentCell As Range
Dim x As String
Dim y As String
Dim z As String

CountBox = WorksheetFunction.CountIf(Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row), "Box")

Set CurrentCell = Range("A1")
For i = 1 To CountBox
    Set CurrentCell = Cells.Find(What:="Box", After:=CurrentCell, SearchDirection:=xlNext, MatchCase:=True)
    
    x = CurrentCell.Offset(0, 4).Address
    y = CurrentCell.Offset(0, 4).Address
    z = CurrentCell.Offset(1, 5).Address
Next

Regards