If Then Conditionality Help

[Jbm]

First, sorry if this is a double post but it seems my previous attempt
disappeared...

I'm running Excel 2007, and with the starting point of Luke M, I was able to
write this macro:

Sub DataMove()
RowCount = 2
For Each c In Range("B:B")
If c.Value Like "Wooden Shoes" Then
Cells(RowCount, "H").Value = c.Value
Cells(RowCount, "I").Value = c.Offset(0, 1).Value
Cells(RowCount, "J").Value = c.Offset(0, 2).Value
Cells(RowCount, "K").Value = c.Offset(0, 3).Value
Cells(RowCount, "G").Value = c.Offset(0, -1).Value
RowCount = RowCount + 1
End If
Next
End Sub

There are two things I want this macro to do that I can't figure out:
First, I'd like for the macro to search for terms that a cell contains,
instead of the exact values. So I'd like to use "Wood" instead of "Wooden
Shoes" so that my results include things like "Hardwood Floors" as well as
"Wooden Shoes." I'm unsure of what to use instead of 'Like' to do this.
Second, I have about ten separate terms I need to search for, such as "Tile"
and "Soft" and "Wood" instead of only "Wood." I know with 'elseif' I can
repeat lines 4-10, replacing "Wooden Shoes" with "Tile," but that's 7 extra
lines per search term, and the macro balloons in size when I have so many
search terms. Is there a way for the macro to return results for cells that
contain "Tile" and/or "Soft" and/or "Wood" using fewer lines of code?
Once again apologies if this is a double post, I waited about 20 minutes and
the post still seemed to have disappeared. Thanks in advance for any and all
help.

 

[ytayta555]

If c.Value Like "Wooden Shoes" Then
Cells(RowCount, "H").Value = c.Value

Try to work with wildcards , such as :

If c.Value Like "*Wood*" Then ,
and "Wood*" and "*Wood"

 

[Matthew Herbert]

First, sorry if this is a double post but it seems my previous attempt
disappeared...

I'm running Excel 2007, and with the starting point of Luke M, I was ableto
write this macro:

Sub DataMove()
RowCount = 2
For Each c In Range("B:B")
If c.Value Like "Wooden Shoes" Then
Cells(RowCount, "H").Value = c.Value
Cells(RowCount, "I").Value = c.Offset(0, 1).Value
Cells(RowCount, "J").Value = c.Offset(0, 2).Value
Cells(RowCount, "K").Value = c.Offset(0, 3).Value
Cells(RowCount, "G").Value = c.Offset(0, -1).Value
RowCount = RowCount + 1
End If
Next
End Sub

There are two things I want this macro to do that I can't figure out:
First, I'd like for the macro to search for terms that a cell contains,
instead of the exact values.  So I'd like to use "Wood" instead of "Wooden
Shoes" so that my results include things like "Hardwood Floors" as well as
"Wooden Shoes."  I'm unsure of what to use instead of 'Like' to do this..
Second, I have about ten separate terms I need to search for, such as "Tile"
and "Soft" and "Wood" instead of only "Wood."  I know with 'elseif' I can
repeat lines 4-10, replacing "Wooden Shoes" with "Tile," but that's 7 extra
lines per search term, and the macro balloons in size when I have so many
search terms.  Is there a way for the macro to return results for cellsthat
contain "Tile" and/or "Soft" and/or "Wood" using fewer lines of code?
Once again apologies if this is a double post, I waited about 20 minutes and
the post still seemed to have disappeared.  Thanks in advance for any and all
help.

Jbm,

I didn't test any of the code below, but the code should help point
you in the right direction. There are two different sub procedures
(FindCellByCell and FindMultipleCells) and one function (FindAll).
The FindAll function is a derivation of what you'll see in the "Find
Method" in the VBE help documentation (which I recommend you read).

Best,

Matthew Herbert

Sub FindCellByCell()
Dim rngCell As Range
Dim varSearch As Variant
Dim intSearch As Integer

varSearch = Array("wood", "tile", "soft")

For Each rngCell In ActiveSheet.Range("B:B").Cells
For intSearch = LBound(varSearch) To UBound(varSearch)
'you could use the Like operator here instead
If InStr(1, rngCell.Text, varSearch(intSearch)) <> 0 Then
MsgBox "Found the search term " & varSearch(intSearch) & _
" in " & rngCell.Address(False, False) & "."
End If
Next intSearch
Next rngCell

End Sub

Sub FindMultipleCells()
Dim varSearch As Variant
Dim rngSearch As Range
Dim rngFound As Range

varSearch = Array("wood", "tile", "soft")
Set rngSearch = ActiveSheet.Range("B:B")

For intSearch = LBound(varSearch) To UBound(varSearch)
Set rngFound = FindAll(rngSearch, "*" & varSearch(intSearch) &
"*")

If Not rngFound Is Nothing Then
MsgBox "Found the search term " & varSearch(intSearch) & _
" at the following location(s): " & rngCell.Address
(False, False) & "."
End If
Next intSearch

End Sub
Function FindAll(rngSearch As Range, varFindWhat As Variant, _
Optional LookIn As XlFindLookIn = xlValues, _
Optional LookAt As XlLookAt = xlPart, _
Optional SearchOrder As XlSearchOrder = xlByRows, _
Optional SearchDirection As XlSearchDirection =
xlNext, _
Optional MatchCase As Boolean = False, _
Optional MatchByte As Boolean = False, _
Optional SearchFormat As Boolean = False) As Range

Dim rngLastCell As Range
Dim rngFound As Range
Dim rngFirstFound As Range
Dim rngListFound As Range

Set rngLastCell = rngSearch.Cells(rngSearch.Cells.Count)
Set rngFound = rngSearch.Find(What:=varFindWhat, _
After:=rngLastCell, _
LookIn:=LookIn, _
LookAt:=LookAt, _
SearchDirection:=SearchDirection, _
SearchOrder:=SearchOrder, _
MatchCase:=MatchCase, _
MatchByte:=MatchByte, _
SearchFormat:=SearchFormat)

If Not rngFound Is Nothing Then
Set rngFirstFound = rngFound
Set rngListFound = rngFound
Set rngFound = rngSearch.FindNext(After:=rngFound)
Do
If rngFound.Address = rngFirstFound.Address Then Exit Do
Set rngListFound = Application.Union(rngListFound, rngFound)
Set rngFound = rngSearch.FindNext(After:=rngFound)
Loop
End If

If rngFound Is Nothing Then
Set FindAll = Nothing
Else
Set FindAll = rngListFound
End If

End Function