Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
If Then Conditionality Help
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
If Then Conditionality Help
On 27 iul., 20:00, Jbm wrote:
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" |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
If Then Conditionality Help
On Jul 27, 11:00*am, Jbm wrote:
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. 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditionality - row to repeat at top | Excel Discussion (Misc queries) |