how to search for data in a range of cells next to another range of cells

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:

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
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Forum statistics

Threads
1,224,548
Messages
6,179,445
Members
452,915
Latest member
hannnahheileen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top