Hi,

I've seen threads similar to this before, however I don't know much about macros so I'm having trouble translating similar macros into one that fits the column/cell/sheet names as mine.

So in Sheet1 of my file I want to copy out all rows that contain "Milka" in column I. This column contains other names in each cell, but I just want to take out ones that contain Milka (can contain other names also).

And I want to copy these rows into Sheet 5.

Any help from you excel geniuses would be much appreciated! :)

Hi,

Am Mon, 28 Jan 2013 16:38:38 +0000 schrieb K.Fell:

So in Sheet1 of my file I want to copy out all rows that contain "Milka"
in column I. This column contains other names in each cell, but I just
want to take out ones that contain Milka (can contain other names
also).

try:
Sub Test()
Dim LRow1 As Long
Dim LRow5 As Long
Dim rngC As Range

With Sheets("Sheet1")
LRow1 = .Cells(.Rows.Count, 1).End(xlUp).Row
For Each rngC In .Range("I1:I" & LRow1)
If rngC = "Milka" Then
LRow5 = Sheets("Sheet5").Cells(Rows.Count, 1).End(xlUp).Row + 1
rngC.EntireRow.Copy Sheets("Sheet5").Cells(LRow5, 1)
End If
Next
End With
End Sub



Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Claus Busch wrote:

Am Mon, 28 Jan 2013 16:38:38 +0000 schrieb K.Fell:

So in Sheet1 of my file I want to copy out all rows that contain "Milka"
in column I. This column contains other names in each cell, but I just
want to take out ones that contain Milka (can contain other names
also).

try:
Sub Test()
Dim LRow1 As Long
Dim LRow5 As Long
Dim rngC As Range

With Sheets("Sheet1")
LRow1 = .Cells(.Rows.Count, 1).End(xlUp).Row
For Each rngC In .Range("I1:I" & LRow1)
If rngC = "Milka" Then

I read the OP's message to read that the cell might contain multiple names,
in which case the above line needs to be changed to this:

If InStr(rngC, "Milka") Then

LRow5 = Sheets("Sheet5").Cells(Rows.Count, 1).End(xlUp).Row + 1
rngC.EntireRow.Copy Sheets("Sheet5").Cells(LRow5, 1)
End If
Next
End With
End Sub

--
- In short, I'm an actress you won't make cry.
- Which brings us back to the question, "Why would I want to cast you?"

You might want to get into the habit of specifying a value since InStr
can result unexpected behavior if you assume it's return value is equal
to vbFalse...

If InStr(rngC, "Milka") 0 Then

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

GS wrote:

You might want to get into the habit of specifying a value since InStr
can result unexpected behavior if you assume it's return value is equal
to vbFalse...

If InStr(rngC, "Milka") 0 Then

I generally just go by the old standby, "0 is false, anything else is true."
It's been working for ~29 years now...

--
Eve was not the first to pluck and sample the apple.
Adam was first and he learned by this to put the blame on Eve.

Auric__ laid this down on his screen :
GS wrote:

You might want to get into the habit of specifying a value since InStr
can result unexpected behavior if you assume it's return value is equal
to vbFalse...

If InStr(rngC, "Milka") 0 Then

I generally just go by the old standby, "0 is false, anything else is true."
It's been working for ~29 years now...

That's safe in VB but VBA will bite you with that assumption!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

GS wrote:

Auric__ laid this down on his screen :
GS wrote:

You might want to get into the habit of specifying a value since InStr
can result unexpected behavior if you assume it's return value is
equal to vbFalse...

If InStr(rngC, "Milka") 0 Then

I generally just go by the old standby, "0 is false, anything else is
true." It's been working for ~29 years now...

That's safe in VB but VBA will bite you with that assumption!

Clarify please? I have several places where I use InStr(x, y) as an implied
boolean value, and have never noticed a problem.

--
It has to be an order. Armies don't move because a commander says
"I suppose it's time to attack."

Auric__ pretended :
GS wrote:

Auric__ laid this down on his screen :
GS wrote:

You might want to get into the habit of specifying a value since InStr
can result unexpected behavior if you assume it's return value is
equal to vbFalse...

If InStr(rngC, "Milka") 0 Then

I generally just go by the old standby, "0 is false, anything else is
true." It's been working for ~29 years now...

That's safe in VB but VBA will bite you with that assumption!

Clarify please? I have several places where I use InStr(x, y) as an implied
boolean value, and have never noticed a problem.

I can't give you explicit examples because I stopped using it as an
"implied boolean" after several occasions where it just didn't work as
assumed. So if you haven't encountered any gliches then you've been
lucky so far. Many others have not!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Auric__ wrote :
GS wrote:

Auric__ laid this down on his screen :
GS wrote:

You might want to get into the habit of specifying a value since InStr
can result unexpected behavior if you assume it's return value is
equal to vbFalse...

If InStr(rngC, "Milka") 0 Then

I generally just go by the old standby, "0 is false, anything else is
true." It's been working for ~29 years now...

That's safe in VB but VBA will bite you with that assumption!

Clarify please? I have several places where I use InStr(x, y) as an implied
boolean value, and have never noticed a problem.

See Karl's explanation under the topic "Boolean games" in
microsoft.public.vb.general for clarification of my point. Clearly it
doesn't apply to your usage here, but Karl notes the exceptions where
it doesn't work in VB/VBA as expected.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion