I need help creating a macro that will search colums B for "Yes" when it
finds the first one I need a new row added above with the word "mand"
incolumn A. Then search for the first "No" in column B and do the same except
add the word Opt
Need to go from this:
tom yes
john yes
bob yes
gill yes
gail No
peter No

To this:
Mand
tom yes
john yes
bob yes
gill yes
Opt
gail No
peter No

With data in colA and ColB from Row1; try the below macro

Sub MyMacro()
Dim lngRow As Long, varData As Variant
lngRow = 1
Do While Range("A" & lngRow) < ""
If varData < UCase(Range("B" & lngRow)) Then
If UCase(Range("B" & lngRow)) = "YES" Then
Rows(lngRow).Insert: Range("A" & lngRow) = "mand"
lngRow = lngRow + 1
ElseIf UCase(Range("B" & lngRow)) = "NO" Then
Rows(lngRow).Insert: Range("A" & lngRow) = "Opt"
lngRow = lngRow + 1
End If
End If
varData = UCase(Range("B" & lngRow))
lngRow = lngRow + 1
Loop
End Sub
--
If this post helps click Yes
---------------
Jacob Skaria


"Bobbo" wrote:

I need help creating a macro that will search colums B for "Yes" when it
finds the first one I need a new row added above with the word "mand"
incolumn A. Then search for the first "No" in column B and do the same except
add the word Opt
Need to go from this:
tom yes
john yes
bob yes
gill yes
gail No
peter No

To this:
Mand
tom yes
john yes
bob yes
gill yes
Opt
gail No
peter No

Give this macro a try...

Sub MandOpt()
Dim R As Range
Set R = Columns("B").Find("Yes", MatchCase:=False, LookAt:=xlWhole)
R.EntireRow.Insert xlDown
R.Offset(-1, -1).Value = "Mand"
Set R = Columns("B").Find("No", After:=R, _
MatchCase:=False, LookAt:=xlWhole)
R.EntireRow.Insert xlDown
R.Offset(-1, -1).Value = "Opt"
End Sub

--
Rick (MVP - Excel)


"Bobbo" wrote in message
...
I need help creating a macro that will search colums B for "Yes" when it
finds the first one I need a new row added above with the word "mand"
incolumn A. Then search for the first "No" in column B and do the same
except
add the word Opt
Need to go from this:
tom yes
john yes
bob yes
gill yes
gail No
peter No

To this:
Mand
tom yes
john yes
bob yes
gill yes
Opt
gail No
peter No