Copying row into new sheet if it contains certain text

[K.Fell]

Hi,

I've seen threads similar to this before, however I don't know muc
about macros so I'm having trouble translating similar macros into on
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 jus
want to take out ones that contain Milka (can contain other name
also).

And I want to copy these rows into Sheet 5.

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

 

[Claus Busch]

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

 

[Auric__]

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


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

 

[GS]

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

 

[Auric__]

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...

 

[GS]

Auric__ laid this down on his screen :

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

 

[Auric__]

Auric__ laid this down on his screen :

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.

 

[GS]

Auric__ pretended :

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

 

[GS]

Auric__ wrote :

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