sweetness34
Board Regular
- Joined
- Jun 23, 2011
- Messages
- 70
Im wondering how to search a range of cells for a string that is next to another range of cells also searching for a string.
What I mean is in column C I have 3 possible categories, lets say Breakfast, Lunch, Dinner. Then in column D I have Apples, Bananas, Oranges.
my spreadsheet looks like this:
C D
Breakfast Apples
Breakfast Apples
Breakfast Apples
Breakfast Bananas
Breakfast Bananas
Breakfast Bananas
Breakfast Bananas
Breakfast Oranges
Lunch Apples
Lunch Apples
Lunch Bananas
Lunch Oranges
Lunch Oranges
Dinner Apples
Dinner Apples
Dinner Bananas
Dinner Bananas
Dinner Oranges
I need to insert 2 rows after each category of fruit. But it needs to treat Breakfast Lunch and Dinner all as seperate categories. So after Apples, Bananas, and Oranges. Also the location of these always changes. What I mean is sometimes there are 2 Breakfast entries and sometimes there are 20, same with Lunch and Dinner. The code I have right now will not use the first column. My code currently looks like this:
but that gives me an error.
I need the spread sheet to look like this:
Breakfast Apples
Breakfast Apples
Breakfast Apples
Breakfast Bananas
Breakfast Bananas
Breakfast Bananas
Breakfast Bananas
Breakfast Oranges
Lunch Apples
Lunch Apples
Lunch Bananas
Lunch Oranges
Lunch Oranges
Dinner Apples
Dinner Apples
Dinner Bananas
Dinner Bananas
Dinner Oranges
What I mean is in column C I have 3 possible categories, lets say Breakfast, Lunch, Dinner. Then in column D I have Apples, Bananas, Oranges.
my spreadsheet looks like this:
C D
Breakfast Apples
Breakfast Apples
Breakfast Apples
Breakfast Bananas
Breakfast Bananas
Breakfast Bananas
Breakfast Bananas
Breakfast Oranges
Lunch Apples
Lunch Apples
Lunch Bananas
Lunch Oranges
Lunch Oranges
Dinner Apples
Dinner Apples
Dinner Bananas
Dinner Bananas
Dinner Oranges
I need to insert 2 rows after each category of fruit. But it needs to treat Breakfast Lunch and Dinner all as seperate categories. So after Apples, Bananas, and Oranges. Also the location of these always changes. What I mean is sometimes there are 2 Breakfast entries and sometimes there are 20, same with Lunch and Dinner. The code I have right now will not use the first column. My code currently looks like this:
Code:
Dim a As String
Dim b As String
Cells.Find(what:="Breakfast", searchdirection:=xlNext, lookat:=xlWhole).Select
a = Selection.Offset(0, 1).Address
Cells.Find(what:="Breakfast", after:=Range("A1"), searchdirection:=xlPrevious).Select
b = Selection.Offset(0, 1).Address
Cells.Find(what:="Apples", LookIn:=Range(a, b)).Select
Cells.Find(what:="Apples", after:=Range(a, b), searchdirection:=xlPrevious).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.EntireRow.Insert shift:=xlUp
ActiveCell.EntireRow.Insert shift:=xlUp
but that gives me an error.
I need the spread sheet to look like this:
Breakfast Apples
Breakfast Apples
Breakfast Apples
Breakfast Bananas
Breakfast Bananas
Breakfast Bananas
Breakfast Bananas
Breakfast Oranges
Lunch Apples
Lunch Apples
Lunch Bananas
Lunch Oranges
Lunch Oranges
Dinner Apples
Dinner Apples
Dinner Bananas
Dinner Bananas
Dinner Oranges