imagine i have two rows: name and color:
mary blue
ken brown
will blue

what i want is having, in another row, just the names that have blue eyes,
like
mary
will

thank you

Try this, Rafael,

Option Explicit

Sub ChooseEyeColor()

Dim i As Integer
Dim combo, name As String

i = 1

Do While Range("A" & i) < ""

If Range("B" & i) = "blue" Then
name = Range("A" & i)
combo = combo & name & ", "
End If

i = i + 1

Loop

Range("A4") = combo

End Sub

"rafael" wrote:

imagine i have two rows: name and color:
mary blue
ken brown
will blue

what i want is having, in another row, just the names that have blue eyes,
like
mary
will

thank you

hi

thanks for helping

i'm sorry - i tried your code but i was wrong - what i wanted was names in a
new column, not row ... :(

by the way: how will i control that future column?

ty

"gpmichal" escreveu:

Try this, Rafael,

Option Explicit

Sub ChooseEyeColor()

Dim i As Integer
Dim combo, name As String

i = 1

Do While Range("A" & i) < ""

If Range("B" & i) = "blue" Then
name = Range("A" & i)
combo = combo & name & ", "
End If

i = i + 1

Loop

Range("A4") = combo

End Sub

"rafael" wrote:

imagine i have two rows: name and color:
mary blue
ken brown
will blue

what i want is having, in another row, just the names that have blue eyes,
like
mary
will

thank you

Oh...Ok. Can you simply use the folliwing "IF" formula in the cells in
Column "C"?

=IF(B1="blue",A1,"")

Let me know if that will do. If not, we can write some code to do the same
thing.

GP

"rafael" wrote:

hi

thanks for helping

i'm sorry - i tried your code but i was wrong - what i wanted was names in a
new column, not row ... :(

by the way: how will i control that future column?

ty

"gpmichal" escreveu:

Try this, Rafael,

Option Explicit

Sub ChooseEyeColor()

Dim i As Integer
Dim combo, name As String

i = 1

Do While Range("A" & i) < ""

If Range("B" & i) = "blue" Then
name = Range("A" & i)
combo = combo & name & ", "
End If

i = i + 1

Loop

Range("A4") = combo

End Sub

"rafael" wrote:

imagine i have two rows: name and color:
mary blue
ken brown
will blue

what i want is having, in another row, just the names that have blue eyes,
like
mary
will

thank you

ty for helping

i'm using:
=IF(B1="blue";A1;"")

but get a #NAME error, i guess

if instead i use coma, i get a msg error saying formula has an error, which
i cant sort out ... :(

"gpmichal" wrote:

Oh...Ok. Can you simply use the folliwing "IF" formula in the cells in
Column "C"?

=IF(B1="blue",A1,"")

Let me know if that will do. If not, we can write some code to do the same
thing.

GP

"rafael" wrote:

hi

thanks for helping

i'm sorry - i tried your code but i was wrong - what i wanted was names in a
new column, not row ... :(

by the way: how will i control that future column?

ty

"gpmichal" escreveu:

Try this, Rafael,

Option Explicit

Sub ChooseEyeColor()

Dim i As Integer
Dim combo, name As String

i = 1

Do While Range("A" & i) < ""

If Range("B" & i) = "blue" Then
name = Range("A" & i)
combo = combo & name & ", "
End If

i = i + 1

Loop

Range("A4") = combo

End Sub

"rafael" wrote:

imagine i have two rows: name and color:
mary blue
ken brown
will blue

what i want is having, in another row, just the names that have blue eyes,
like
mary
will

thank you

Rafael,

You're using semi-colons in your formula instead of commas. Try it after
you make the change.

GP

"rafael" wrote:

ty for helping

i'm using:
=IF(B1="blue";A1;"")

but get a #NAME error, i guess

if instead i use coma, i get a msg error saying formula has an error, which
i cant sort out ... :(

"gpmichal" wrote:

Oh...Ok. Can you simply use the folliwing "IF" formula in the cells in
Column "C"?

=IF(B1="blue",A1,"")

Let me know if that will do. If not, we can write some code to do the same
thing.

GP

"rafael" wrote:

hi

thanks for helping

i'm sorry - i tried your code but i was wrong - what i wanted was names in a
new column, not row ... :(

by the way: how will i control that future column?

ty

"gpmichal" escreveu:

Try this, Rafael,

Option Explicit

Sub ChooseEyeColor()

Dim i As Integer
Dim combo, name As String

i = 1

Do While Range("A" & i) < ""

If Range("B" & i) = "blue" Then
name = Range("A" & i)
combo = combo & name & ", "
End If

i = i + 1

Loop

Range("A4") = combo

End Sub

"rafael" wrote:

imagine i have two rows: name and color:
mary blue
ken brown
will blue

what i want is having, in another row, just the names that have blue eyes,
like
mary
will

thank you

THANKS AGAIN FOR HEELPING

I USED:
=SE(B1="blue";A1;"")

AND IT WORKS, WITH A CATCH:
I NEED TO ELIMINATE ALL THOSE EMPTY CELLS IN MY NEW COLUMN THAT DONT DISPLAY
DATA AT ALL
IS THAT POSSIBLE?

TY AGAIN

sorry caps :((

"gpmichal" escreveu:

Rafael,

You're using semi-colons in your formula instead of commas. Try it after
you make the change.

GP

"rafael" wrote:

ty for helping

i'm using:
=IF(B1="blue";A1;"")

but get a #NAME error, i guess

if instead i use coma, i get a msg error saying formula has an error, which
i cant sort out ... :(

"gpmichal" wrote:

Oh...Ok. Can you simply use the folliwing "IF" formula in the cells in
Column "C"?

=IF(B1="blue",A1,"")

Let me know if that will do. If not, we can write some code to do the same
thing.

GP

"rafael" wrote:

hi

thanks for helping

i'm sorry - i tried your code but i was wrong - what i wanted was names in a
new column, not row ... :(

by the way: how will i control that future column?

ty

"gpmichal" escreveu:

Try this, Rafael,

Option Explicit

Sub ChooseEyeColor()

Dim i As Integer
Dim combo, name As String

i = 1

Do While Range("A" & i) < ""

If Range("B" & i) = "blue" Then
name = Range("A" & i)
combo = combo & name & ", "
End If

i = i + 1

Loop

Range("A4") = combo

End Sub

"rafael" wrote:

imagine i have two rows: name and color:
mary blue
ken brown
will blue

what i want is having, in another row, just the names that have blue eyes,
like
mary
will

thank you

Yes, it can be done. Do you want to delete the entire row if the cell in Col
C is blank? Or, would you rather not delete the rows and just have Col C
list the names consecutively?

"rafael" wrote:

THANKS AGAIN FOR HEELPING

I USED:
=SE(B1="blue";A1;"")

AND IT WORKS, WITH A CATCH:
I NEED TO ELIMINATE ALL THOSE EMPTY CELLS IN MY NEW COLUMN THAT DONT DISPLAY
DATA AT ALL
IS THAT POSSIBLE?

TY AGAIN

sorry caps :((

"gpmichal" escreveu:

Rafael,

You're using semi-colons in your formula instead of commas. Try it after
you make the change.

GP

"rafael" wrote:

ty for helping

i'm using:
=IF(B1="blue";A1;"")

but get a #NAME error, i guess

if instead i use coma, i get a msg error saying formula has an error, which
i cant sort out ... :(

"gpmichal" wrote:

Oh...Ok. Can you simply use the folliwing "IF" formula in the cells in
Column "C"?

=IF(B1="blue",A1,"")

Let me know if that will do. If not, we can write some code to do the same
thing.

GP

"rafael" wrote:

hi

thanks for helping

i'm sorry - i tried your code but i was wrong - what i wanted was names in a
new column, not row ... :(

by the way: how will i control that future column?

ty

"gpmichal" escreveu:

Try this, Rafael,

Option Explicit

Sub ChooseEyeColor()

Dim i As Integer
Dim combo, name As String

i = 1

Do While Range("A" & i) < ""

If Range("B" & i) = "blue" Then
name = Range("A" & i)
combo = combo & name & ", "
End If

i = i + 1

Loop

Range("A4") = combo

End Sub

"rafael" wrote:

imagine i have two rows: name and color:
mary blue
ken brown
will blue

what i want is having, in another row, just the names that have blue eyes,
like
mary
will

thank you

, would you rather not delete the rows and just have Col C
list the names consecutively?
yes, i guess that would work - (if i just cant rid of them :)) gotta test it
first, of course

ty again!

"gpmichal" escreveu:

Yes, it can be done. Do you want to delete the entire row if the cell in Col
C is blank? Or, would you rather not delete the rows and just have Col C
list the names consecutively?

"rafael" wrote:

THANKS AGAIN FOR HEELPING

I USED:
=SE(B1="blue";A1;"")

AND IT WORKS, WITH A CATCH:
I NEED TO ELIMINATE ALL THOSE EMPTY CELLS IN MY NEW COLUMN THAT DONT DISPLAY
DATA AT ALL
IS THAT POSSIBLE?

TY AGAIN

sorry caps :((

"gpmichal" escreveu:

Rafael,

You're using semi-colons in your formula instead of commas. Try it after
you make the change.

GP

"rafael" wrote:

ty for helping

i'm using:
=IF(B1="blue";A1;"")

but get a #NAME error, i guess

if instead i use coma, i get a msg error saying formula has an error, which
i cant sort out ... :(

"gpmichal" wrote:

Oh...Ok. Can you simply use the folliwing "IF" formula in the cells in
Column "C"?

=IF(B1="blue",A1,"")

Let me know if that will do. If not, we can write some code to do the same
thing.

GP

"rafael" wrote:

hi

thanks for helping

i'm sorry - i tried your code but i was wrong - what i wanted was names in a
new column, not row ... :(

by the way: how will i control that future column?

ty

"gpmichal" escreveu:

Try this, Rafael,

Option Explicit

Sub ChooseEyeColor()

Dim i As Integer
Dim combo, name As String

i = 1

Do While Range("A" & i) < ""

If Range("B" & i) = "blue" Then
name = Range("A" & i)
combo = combo & name & ", "
End If

i = i + 1

Loop

Range("A4") = combo

End Sub

"rafael" wrote:

imagine i have two rows: name and color:
mary blue
ken brown
will blue

what i want is having, in another row, just the names that have blue eyes,
like
mary
will

thank you

Before I look into this any further, I need to know which version of Excel
you're using. I find it uncommon, at least in my experience, that you're
able to use semi-colons in your formulas instead of commas. Also, when I
tried the SE formula you used, all I got was the #Name error and if I tried
it with semi-colons, I got the formula has an error msg. Furthermore, when I
read what the SE formula is supposed to do, I don't see how you're even using
it for the intended purpose. It says it's supposed to return the number of
the first character in a string that matches the criteria e.g.,

=SEARCH("n","printer")

This function returns 4 because "n" is the fourth character in the word
"printer."

You can also search for words within other words. For example, the function

=SEARCH("base","database")


"rafael" wrote:

, would you rather not delete the rows and just have Col C
list the names consecutively?
yes, i guess that would work - (if i just cant rid of them :)) gotta test it
first, of course

ty again!

"gpmichal" escreveu:

Yes, it can be done. Do you want to delete the entire row if the cell in Col
C is blank? Or, would you rather not delete the rows and just have Col C
list the names consecutively?

"rafael" wrote:

THANKS AGAIN FOR HEELPING

I USED:
=SE(B1="blue";A1;"")

AND IT WORKS, WITH A CATCH:
I NEED TO ELIMINATE ALL THOSE EMPTY CELLS IN MY NEW COLUMN THAT DONT DISPLAY
DATA AT ALL
IS THAT POSSIBLE?

TY AGAIN

sorry caps :((

"gpmichal" escreveu:

Rafael,

You're using semi-colons in your formula instead of commas. Try it after
you make the change.

GP

"rafael" wrote:

ty for helping

i'm using:
=IF(B1="blue";A1;"")

but get a #NAME error, i guess

if instead i use coma, i get a msg error saying formula has an error, which
i cant sort out ... :(

"gpmichal" wrote:

Oh...Ok. Can you simply use the folliwing "IF" formula in the cells in
Column "C"?

=IF(B1="blue",A1,"")

Let me know if that will do. If not, we can write some code to do the same
thing.

GP

"rafael" wrote:

hi

thanks for helping

i'm sorry - i tried your code but i was wrong - what i wanted was names in a
new column, not row ... :(

by the way: how will i control that future column?

ty

"gpmichal" escreveu:

Try this, Rafael,

Option Explicit

Sub ChooseEyeColor()

Dim i As Integer
Dim combo, name As String

i = 1

Do While Range("A" & i) < ""

If Range("B" & i) = "blue" Then
name = Range("A" & i)
combo = combo & name & ", "
End If

i = i + 1

Loop

Range("A4") = combo

End Sub

"rafael" wrote:

imagine i have two rows: name and color:
mary blue
ken brown
will blue

what i want is having, in another row, just the names that have blue eyes,
like
mary
will

thank you

gp:
i have excel 2000 in portuguese PT

"gpmichal" escreveu:

Before I look into this any further, I need to know which version of Excel
you're using. I find it uncommon, at least in my experience, that you're
able to use semi-colons in your formulas instead of commas. Also, when I
tried the SE formula you used, all I got was the #Name error and if I tried
it with semi-colons, I got the formula has an error msg. Furthermore, when I
read what the SE formula is supposed to do, I don't see how you're even using
it for the intended purpose. It says it's supposed to return the number of
the first character in a string that matches the criteria e.g.,

=SEARCH("n","printer")

This function returns 4 because "n" is the fourth character in the word
"printer."

You can also search for words within other words. For example, the function

=SEARCH("base","database")


"rafael" wrote:

, would you rather not delete the rows and just have Col C
list the names consecutively?
yes, i guess that would work - (if i just cant rid of them :)) gotta test it
first, of course

ty again!

"gpmichal" escreveu:

Yes, it can be done. Do you want to delete the entire row if the cell in Col
C is blank? Or, would you rather not delete the rows and just have Col C
list the names consecutively?

"rafael" wrote:

THANKS AGAIN FOR HEELPING

I USED:
=SE(B1="blue";A1;"")

AND IT WORKS, WITH A CATCH:
I NEED TO ELIMINATE ALL THOSE EMPTY CELLS IN MY NEW COLUMN THAT DONT DISPLAY
DATA AT ALL
IS THAT POSSIBLE?

TY AGAIN

sorry caps :((

"gpmichal" escreveu:

Rafael,

You're using semi-colons in your formula instead of commas. Try it after
you make the change.

GP

"rafael" wrote:

ty for helping

i'm using:
=IF(B1="blue";A1;"")

but get a #NAME error, i guess

if instead i use coma, i get a msg error saying formula has an error, which
i cant sort out ... :(

"gpmichal" wrote:

Oh...Ok. Can you simply use the folliwing "IF" formula in the cells in
Column "C"?

=IF(B1="blue",A1,"")

Let me know if that will do. If not, we can write some code to do the same
thing.

GP

"rafael" wrote:

hi

thanks for helping

i'm sorry - i tried your code but i was wrong - what i wanted was names in a
new column, not row ... :(

by the way: how will i control that future column?

ty

"gpmichal" escreveu:

Try this, Rafael,

Option Explicit

Sub ChooseEyeColor()

Dim i As Integer
Dim combo, name As String

i = 1

Do While Range("A" & i) < ""

If Range("B" & i) = "blue" Then
name = Range("A" & i)
combo = combo & name & ", "
End If

i = i + 1

Loop

Range("A4") = combo

End Sub

"rafael" wrote:

imagine i have two rows: name and color:
mary blue
ken brown
will blue

what i want is having, in another row, just the names that have blue eyes,
like
mary
will

thank you

i have excel 2000 in portuguese PT

"gpmichal" escreveu:

Before I look into this any further, I need to know which version of Excel
you're using. I find it uncommon, at least in my experience, that you're
able to use semi-colons in your formulas instead of commas. Also, when I
tried the SE formula you used, all I got was the #Name error and if I tried
it with semi-colons, I got the formula has an error msg. Furthermore, when I
read what the SE formula is supposed to do, I don't see how you're even using
it for the intended purpose. It says it's supposed to return the number of
the first character in a string that matches the criteria e.g.,

=SEARCH("n","printer")

This function returns 4 because "n" is the fourth character in the word
"printer."

You can also search for words within other words. For example, the function

=SEARCH("base","database")


"rafael" wrote:

, would you rather not delete the rows and just have Col C
list the names consecutively?
yes, i guess that would work - (if i just cant rid of them :)) gotta test it
first, of course

ty again!

"gpmichal" escreveu:

Yes, it can be done. Do you want to delete the entire row if the cell in Col
C is blank? Or, would you rather not delete the rows and just have Col C
list the names consecutively?

"rafael" wrote:

THANKS AGAIN FOR HEELPING

I USED:
=SE(B1="blue";A1;"")

AND IT WORKS, WITH A CATCH:
I NEED TO ELIMINATE ALL THOSE EMPTY CELLS IN MY NEW COLUMN THAT DONT DISPLAY
DATA AT ALL
IS THAT POSSIBLE?

TY AGAIN

sorry caps :((

"gpmichal" escreveu:

Rafael,

You're using semi-colons in your formula instead of commas. Try it after
you make the change.

GP

"rafael" wrote:

ty for helping

i'm using:
=IF(B1="blue";A1;"")

but get a #NAME error, i guess

if instead i use coma, i get a msg error saying formula has an error, which
i cant sort out ... :(

"gpmichal" wrote:

Oh...Ok. Can you simply use the folliwing "IF" formula in the cells in
Column "C"?

=IF(B1="blue",A1,"")

Let me know if that will do. If not, we can write some code to do the same
thing.

GP

"rafael" wrote:

hi

thanks for helping

i'm sorry - i tried your code but i was wrong - what i wanted was names in a
new column, not row ... :(

by the way: how will i control that future column?

ty

"gpmichal" escreveu:

Try this, Rafael,

Option Explicit

Sub ChooseEyeColor()

Dim i As Integer
Dim combo, name As String

i = 1

Do While Range("A" & i) < ""

If Range("B" & i) = "blue" Then
name = Range("A" & i)
combo = combo & name & ", "
End If

i = i + 1

Loop

Range("A4") = combo

End Sub

"rafael" wrote:

imagine i have two rows: name and color:
mary blue
ken brown
will blue

what i want is having, in another row, just the names that have blue eyes,
like
mary
will

thank you

Ahhhh...that explains it. You're probably using a Spanish keyboard. My wife
is from Mexico and I noticed that Spanish keyboards are very different from
American ones. Probably has formulas set up different too.

Any way, I'm using Excel 2007 (USA) so I'm not sure if the code I write will
work in Excel 2000. Did that first VBA code I wrote work in your Excel
(aside from adding the results to a row instead of a column)?

"rafael" wrote:

i have excel 2000 in portuguese PT

"gpmichal" escreveu:

Before I look into this any further, I need to know which version of Excel
you're using. I find it uncommon, at least in my experience, that you're
able to use semi-colons in your formulas instead of commas. Also, when I
tried the SE formula you used, all I got was the #Name error and if I tried
it with semi-colons, I got the formula has an error msg. Furthermore, when I
read what the SE formula is supposed to do, I don't see how you're even using
it for the intended purpose. It says it's supposed to return the number of
the first character in a string that matches the criteria e.g.,

=SEARCH("n","printer")

This function returns 4 because "n" is the fourth character in the word
"printer."

You can also search for words within other words. For example, the function

=SEARCH("base","database")


"rafael" wrote:

, would you rather not delete the rows and just have Col C
list the names consecutively?
yes, i guess that would work - (if i just cant rid of them :)) gotta test it
first, of course

ty again!

"gpmichal" escreveu:

Yes, it can be done. Do you want to delete the entire row if the cell in Col
C is blank? Or, would you rather not delete the rows and just have Col C
list the names consecutively?

"rafael" wrote:

THANKS AGAIN FOR HEELPING

I USED:
=SE(B1="blue";A1;"")

AND IT WORKS, WITH A CATCH:
I NEED TO ELIMINATE ALL THOSE EMPTY CELLS IN MY NEW COLUMN THAT DONT DISPLAY
DATA AT ALL
IS THAT POSSIBLE?

TY AGAIN

sorry caps :((

"gpmichal" escreveu:

Rafael,

You're using semi-colons in your formula instead of commas. Try it after
you make the change.

GP

"rafael" wrote:

ty for helping

i'm using:
=IF(B1="blue";A1;"")

but get a #NAME error, i guess

if instead i use coma, i get a msg error saying formula has an error, which
i cant sort out ... :(

"gpmichal" wrote:

Oh...Ok. Can you simply use the folliwing "IF" formula in the cells in
Column "C"?

=IF(B1="blue",A1,"")

Let me know if that will do. If not, we can write some code to do the same
thing.

GP

"rafael" wrote:

hi

thanks for helping

i'm sorry - i tried your code but i was wrong - what i wanted was names in a
new column, not row ... :(

by the way: how will i control that future column?

ty

"gpmichal" escreveu:

Try this, Rafael,

Option Explicit

Sub ChooseEyeColor()

Dim i As Integer
Dim combo, name As String

i = 1

Do While Range("A" & i) < ""

If Range("B" & i) = "blue" Then
name = Range("A" & i)
combo = combo & name & ", "
End If

i = i + 1

Loop

Range("A4") = combo

End Sub

"rafael" wrote:

imagine i have two rows: name and color:
mary blue
ken brown
will blue

what i want is having, in another row, just the names that have blue eyes,
like
mary
will

thank you

not spanish - portuguese! :)

yes the code works ok

"gpmichal" escreveu:

Ahhhh...that explains it. You're probably using a Spanish keyboard. My wife
is from Mexico and I noticed that Spanish keyboards are very different from
American ones. Probably has formulas set up different too.

Any way, I'm using Excel 2007 (USA) so I'm not sure if the code I write will
work in Excel 2000. Did that first VBA code I wrote work in your Excel
(aside from adding the results to a row instead of a column)?

"rafael" wrote:

i have excel 2000 in portuguese PT

"gpmichal" escreveu:

Before I look into this any further, I need to know which version of Excel
you're using. I find it uncommon, at least in my experience, that you're
able to use semi-colons in your formulas instead of commas. Also, when I
tried the SE formula you used, all I got was the #Name error and if I tried
it with semi-colons, I got the formula has an error msg. Furthermore, when I
read what the SE formula is supposed to do, I don't see how you're even using
it for the intended purpose. It says it's supposed to return the number of
the first character in a string that matches the criteria e.g.,

=SEARCH("n","printer")

This function returns 4 because "n" is the fourth character in the word
"printer."

You can also search for words within other words. For example, the function

=SEARCH("base","database")


"rafael" wrote:

, would you rather not delete the rows and just have Col C
list the names consecutively?
yes, i guess that would work - (if i just cant rid of them :)) gotta test it
first, of course

ty again!

"gpmichal" escreveu:

Yes, it can be done. Do you want to delete the entire row if the cell in Col
C is blank? Or, would you rather not delete the rows and just have Col C
list the names consecutively?

"rafael" wrote:

THANKS AGAIN FOR HEELPING

I USED:
=SE(B1="blue";A1;"")

AND IT WORKS, WITH A CATCH:
I NEED TO ELIMINATE ALL THOSE EMPTY CELLS IN MY NEW COLUMN THAT DONT DISPLAY
DATA AT ALL
IS THAT POSSIBLE?

TY AGAIN

sorry caps :((

"gpmichal" escreveu:

Rafael,

You're using semi-colons in your formula instead of commas. Try it after
you make the change.

GP

"rafael" wrote:

ty for helping

i'm using:
=IF(B1="blue";A1;"")

but get a #NAME error, i guess

if instead i use coma, i get a msg error saying formula has an error, which
i cant sort out ... :(

"gpmichal" wrote:

Oh...Ok. Can you simply use the folliwing "IF" formula in the cells in
Column "C"?

=IF(B1="blue",A1,"")

Let me know if that will do. If not, we can write some code to do the same
thing.

GP

"rafael" wrote:

hi

thanks for helping

i'm sorry - i tried your code but i was wrong - what i wanted was names in a
new column, not row ... :(

by the way: how will i control that future column?

ty

"gpmichal" escreveu:

Try this, Rafael,

Option Explicit

Sub ChooseEyeColor()

Dim i As Integer
Dim combo, name As String

i = 1

Do While Range("A" & i) < ""

If Range("B" & i) = "blue" Then
name = Range("A" & i)
combo = combo & name & ", "
End If

i = i + 1

Loop

Range("A4") = combo

End Sub

"rafael" wrote:

imagine i have two rows: name and color:
mary blue
ken brown
will blue

what i want is having, in another row, just the names that have blue eyes,
like
mary
will

thank you

Well allrighty then, Portuguese it is.

Try this, Rafael. It work fine in Excel 2007:

Option Explicit
Sub Select_Names_by_Color()

Dim i, dataCount As Integer
Dim myRange As Range
Set myRange = Range("A:A")
dataCount = Application.WorksheetFunction.CountA(myRange)

i = 1

Do While Range("A" & i) < ""

If Range("B" & i) = "blue" Then
Range("C" & i) = Range("A" & i)
End If

i = i + 1

Loop

For i = dataCount To 1 Step -1

If Range("C" & i) = "" Then
Range("C" & i).Select
Selection.Delete Shift:=xlUp
End If

Next

End Sub



"rafael" wrote:

not spanish - portuguese! :)

yes the code works ok

"gpmichal" escreveu:

Ahhhh...that explains it. You're probably using a Spanish keyboard. My wife
is from Mexico and I noticed that Spanish keyboards are very different from
American ones. Probably has formulas set up different too.

Any way, I'm using Excel 2007 (USA) so I'm not sure if the code I write will
work in Excel 2000. Did that first VBA code I wrote work in your Excel
(aside from adding the results to a row instead of a column)?

"rafael" wrote:

i have excel 2000 in portuguese PT

"gpmichal" escreveu:

Before I look into this any further, I need to know which version of Excel
you're using. I find it uncommon, at least in my experience, that you're
able to use semi-colons in your formulas instead of commas. Also, when I
tried the SE formula you used, all I got was the #Name error and if I tried
it with semi-colons, I got the formula has an error msg. Furthermore, when I
read what the SE formula is supposed to do, I don't see how you're even using
it for the intended purpose. It says it's supposed to return the number of
the first character in a string that matches the criteria e.g.,

=SEARCH("n","printer")

This function returns 4 because "n" is the fourth character in the word
"printer."

You can also search for words within other words. For example, the function

=SEARCH("base","database")


"rafael" wrote:

, would you rather not delete the rows and just have Col C
list the names consecutively?
yes, i guess that would work - (if i just cant rid of them :)) gotta test it
first, of course

ty again!

"gpmichal" escreveu:

Yes, it can be done. Do you want to delete the entire row if the cell in Col
C is blank? Or, would you rather not delete the rows and just have Col C
list the names consecutively?

"rafael" wrote:

THANKS AGAIN FOR HEELPING

I USED:
=SE(B1="blue";A1;"")

AND IT WORKS, WITH A CATCH:
I NEED TO ELIMINATE ALL THOSE EMPTY CELLS IN MY NEW COLUMN THAT DONT DISPLAY
DATA AT ALL
IS THAT POSSIBLE?

TY AGAIN

sorry caps :((

"gpmichal" escreveu:

Rafael,

You're using semi-colons in your formula instead of commas. Try it after
you make the change.

GP

"rafael" wrote:

ty for helping

i'm using:
=IF(B1="blue";A1;"")

but get a #NAME error, i guess

if instead i use coma, i get a msg error saying formula has an error, which
i cant sort out ... :(

"gpmichal" wrote:

Oh...Ok. Can you simply use the folliwing "IF" formula in the cells in
Column "C"?

=IF(B1="blue",A1,"")

Let me know if that will do. If not, we can write some code to do the same
thing.

GP

"rafael" wrote:

hi

thanks for helping

i'm sorry - i tried your code but i was wrong - what i wanted was names in a
new column, not row ... :(

by the way: how will i control that future column?

ty

"gpmichal" escreveu:

Try this, Rafael,

Option Explicit

Sub ChooseEyeColor()

Dim i As Integer
Dim combo, name As String

i = 1

Do While Range("A" & i) < ""

If Range("B" & i) = "blue" Then
name = Range("A" & i)
combo = combo & name & ", "
End If

i = i + 1

Loop

Range("A4") = combo

End Sub

"rafael" wrote:

imagine i have two rows: name and color:
mary blue
ken brown
will blue

what i want is having, in another row, just the names that have blue eyes,
like
mary
will

thank you

yes, it works nicely, though there's a catch

what i exactly need is a bit diferent: the number of rows (yes, rows) varies
- it means i can open or close a row at any time i want

what i want is having that column with the current opened rows names

so, the code is a macro, and that implies i have to run it everytime i close
or open, or even create a new row

is it possible to have code in a cell to acomplish what your macro does?

also, if i close my book and open it again, add a new row and run the macro,
last "blue" name appears repeated :((

thanks again!

"gpmichal" escreveu:

Well allrighty then, Portuguese it is.

Try this, Rafael. It work fine in Excel 2007:

Option Explicit
Sub Select_Names_by_Color()

Dim i, dataCount As Integer
Dim myRange As Range
Set myRange = Range("A:A")
dataCount = Application.WorksheetFunction.CountA(myRange)

i = 1

Do While Range("A" & i) < ""

If Range("B" & i) = "blue" Then
Range("C" & i) = Range("A" & i)
End If

i = i + 1

Loop

For i = dataCount To 1 Step -1

If Range("C" & i) = "" Then
Range("C" & i).Select
Selection.Delete Shift:=xlUp
End If

Next

End Sub



"rafael" wrote:

not spanish - portuguese! :)

yes the code works ok

"gpmichal" escreveu:

Ahhhh...that explains it. You're probably using a Spanish keyboard. My wife
is from Mexico and I noticed that Spanish keyboards are very different from
American ones. Probably has formulas set up different too.

Any way, I'm using Excel 2007 (USA) so I'm not sure if the code I write will
work in Excel 2000. Did that first VBA code I wrote work in your Excel
(aside from adding the results to a row instead of a column)?

"rafael" wrote:

i have excel 2000 in portuguese PT

"gpmichal" escreveu:

Before I look into this any further, I need to know which version of Excel
you're using. I find it uncommon, at least in my experience, that you're
able to use semi-colons in your formulas instead of commas. Also, when I
tried the SE formula you used, all I got was the #Name error and if I tried
it with semi-colons, I got the formula has an error msg. Furthermore, when I
read what the SE formula is supposed to do, I don't see how you're even using
it for the intended purpose. It says it's supposed to return the number of
the first character in a string that matches the criteria e.g.,

=SEARCH("n","printer")

This function returns 4 because "n" is the fourth character in the word
"printer."

You can also search for words within other words. For example, the function

=SEARCH("base","database")


"rafael" wrote:

, would you rather not delete the rows and just have Col C
list the names consecutively?
yes, i guess that would work - (if i just cant rid of them :)) gotta test it
first, of course

ty again!

"gpmichal" escreveu:

Yes, it can be done. Do you want to delete the entire row if the cell in Col
C is blank? Or, would you rather not delete the rows and just have Col C
list the names consecutively?

"rafael" wrote:

THANKS AGAIN FOR HEELPING

I USED:
=SE(B1="blue";A1;"")

AND IT WORKS, WITH A CATCH:
I NEED TO ELIMINATE ALL THOSE EMPTY CELLS IN MY NEW COLUMN THAT DONT DISPLAY
DATA AT ALL
IS THAT POSSIBLE?

TY AGAIN

sorry caps :((

"gpmichal" escreveu:

Rafael,

You're using semi-colons in your formula instead of commas. Try it after
you make the change.

GP

"rafael" wrote:

ty for helping

i'm using:
=IF(B1="blue";A1;"")

but get a #NAME error, i guess

if instead i use coma, i get a msg error saying formula has an error, which
i cant sort out ... :(

"gpmichal" wrote:

Oh...Ok. Can you simply use the folliwing "IF" formula in the cells in
Column "C"?

=IF(B1="blue",A1,"")

Let me know if that will do. If not, we can write some code to do the same
thing.

GP

"rafael" wrote:

hi

thanks for helping

i'm sorry - i tried your code but i was wrong - what i wanted was names in a
new column, not row ... :(

by the way: how will i control that future column?

ty

"gpmichal" escreveu:

Try this, Rafael,

Option Explicit

Sub ChooseEyeColor()

Dim i As Integer
Dim combo, name As String

i = 1

Do While Range("A" & i) < ""

If Range("B" & i) = "blue" Then
name = Range("A" & i)
combo = combo & name & ", "
End If

i = i + 1

Loop

Range("A4") = combo

End Sub

"rafael" wrote:

imagine i have two rows: name and color:
mary blue
ken brown
will blue

what i want is having, in another row, just the names that have blue eyes,
like
mary
will

thank you

i just tried inserting your code in the activate event, and its perfect for
me, because i have original data in one sheet and the "new" column in another
sheet

the only problem is that everytime it activates, it repeates the very last
ocorrence .... :(((

"rafael" escreveu:

yes, it works nicely, though there's a catch

what i exactly need is a bit diferent: the number of rows (yes, rows) varies
- it means i can open or close a row at any time i want

what i want is having that column with the current opened rows names

so, the code is a macro, and that implies i have to run it everytime i close
or open, or even create a new row

is it possible to have code in a cell to acomplish what your macro does?

also, if i close my book and open it again, add a new row and run the macro,
last "blue" name appears repeated :((

thanks again!

"gpmichal" escreveu:

Well allrighty then, Portuguese it is.

Try this, Rafael. It work fine in Excel 2007:

Option Explicit
Sub Select_Names_by_Color()

Dim i, dataCount As Integer
Dim myRange As Range
Set myRange = Range("A:A")
dataCount = Application.WorksheetFunction.CountA(myRange)

i = 1

Do While Range("A" & i) < ""

If Range("B" & i) = "blue" Then
Range("C" & i) = Range("A" & i)
End If

i = i + 1

Loop

For i = dataCount To 1 Step -1

If Range("C" & i) = "" Then
Range("C" & i).Select
Selection.Delete Shift:=xlUp
End If

Next

End Sub



"rafael" wrote:

not spanish - portuguese! :)

yes the code works ok

"gpmichal" escreveu:

Ahhhh...that explains it. You're probably using a Spanish keyboard. My wife
is from Mexico and I noticed that Spanish keyboards are very different from
American ones. Probably has formulas set up different too.

Any way, I'm using Excel 2007 (USA) so I'm not sure if the code I write will
work in Excel 2000. Did that first VBA code I wrote work in your Excel
(aside from adding the results to a row instead of a column)?

"rafael" wrote:

i have excel 2000 in portuguese PT

"gpmichal" escreveu:

Before I look into this any further, I need to know which version of Excel
you're using. I find it uncommon, at least in my experience, that you're
able to use semi-colons in your formulas instead of commas. Also, when I
tried the SE formula you used, all I got was the #Name error and if I tried
it with semi-colons, I got the formula has an error msg. Furthermore, when I
read what the SE formula is supposed to do, I don't see how you're even using
it for the intended purpose. It says it's supposed to return the number of
the first character in a string that matches the criteria e.g.,

=SEARCH("n","printer")

This function returns 4 because "n" is the fourth character in the word
"printer."

You can also search for words within other words. For example, the function

=SEARCH("base","database")


"rafael" wrote:

, would you rather not delete the rows and just have Col C
list the names consecutively?
yes, i guess that would work - (if i just cant rid of them :)) gotta test it
first, of course

ty again!

"gpmichal" escreveu:

Yes, it can be done. Do you want to delete the entire row if the cell in Col
C is blank? Or, would you rather not delete the rows and just have Col C
list the names consecutively?

"rafael" wrote:

THANKS AGAIN FOR HEELPING

I USED:
=SE(B1="blue";A1;"")

AND IT WORKS, WITH A CATCH:
I NEED TO ELIMINATE ALL THOSE EMPTY CELLS IN MY NEW COLUMN THAT DONT DISPLAY
DATA AT ALL
IS THAT POSSIBLE?

TY AGAIN

sorry caps :((

"gpmichal" escreveu:

Rafael,

You're using semi-colons in your formula instead of commas. Try it after
you make the change.

GP

"rafael" wrote:

ty for helping

i'm using:
=IF(B1="blue";A1;"")

but get a #NAME error, i guess

if instead i use coma, i get a msg error saying formula has an error, which
i cant sort out ... :(

"gpmichal" wrote:

Oh...Ok. Can you simply use the folliwing "IF" formula in the cells in
Column "C"?

=IF(B1="blue",A1,"")

Let me know if that will do. If not, we can write some code to do the same
thing.

GP

"rafael" wrote:

hi

thanks for helping

i'm sorry - i tried your code but i was wrong - what i wanted was names in a
new column, not row ... :(

by the way: how will i control that future column?

ty

"gpmichal" escreveu:

Try this, Rafael,

Option Explicit

Sub ChooseEyeColor()

Dim i As Integer
Dim combo, name As String

i = 1

Do While Range("A" & i) < ""

If Range("B" & i) = "blue" Then
name = Range("A" & i)
combo = combo & name & ", "
End If

i = i + 1

Loop

Range("A4") = combo

End Sub

"rafael" wrote:

imagine i have two rows: name and color:
mary blue
ken brown
will blue

what i want is having, in another row, just the names that have blue eyes,
like
mary
will

thank you

Rafael,

I'm having a little bit of trouble understanding the issue where the last
row is duplicating. When I run the procedure, I get the results below.
Perhaps it has something to do with the Activate event??

Mary blue Mary
tom brown gary
bob black Weasel
gary blue
rafael gray
Weasel blue


"rafael" wrote:

i just tried inserting your code in the activate event, and its perfect for
me, because i have original data in one sheet and the "new" column in another
sheet

the only problem is that everytime it activates, it repeates the very last
ocorrence .... :(((

"rafael" escreveu:

yes, it works nicely, though there's a catch

what i exactly need is a bit diferent: the number of rows (yes, rows) varies
- it means i can open or close a row at any time i want

what i want is having that column with the current opened rows names

so, the code is a macro, and that implies i have to run it everytime i close
or open, or even create a new row

is it possible to have code in a cell to acomplish what your macro does?

also, if i close my book and open it again, add a new row and run the macro,
last "blue" name appears repeated :((

thanks again!

"gpmichal" escreveu:

Well allrighty then, Portuguese it is.

Try this, Rafael. It work fine in Excel 2007:

Option Explicit
Sub Select_Names_by_Color()

Dim i, dataCount As Integer
Dim myRange As Range
Set myRange = Range("A:A")
dataCount = Application.WorksheetFunction.CountA(myRange)

i = 1

Do While Range("A" & i) < ""

If Range("B" & i) = "blue" Then
Range("C" & i) = Range("A" & i)
End If

i = i + 1

Loop

For i = dataCount To 1 Step -1

If Range("C" & i) = "" Then
Range("C" & i).Select
Selection.Delete Shift:=xlUp
End If

Next

End Sub



"rafael" wrote:

not spanish - portuguese! :)

yes the code works ok

"gpmichal" escreveu:

Ahhhh...that explains it. You're probably using a Spanish keyboard. My wife
is from Mexico and I noticed that Spanish keyboards are very different from
American ones. Probably has formulas set up different too.

Any way, I'm using Excel 2007 (USA) so I'm not sure if the code I write will
work in Excel 2000. Did that first VBA code I wrote work in your Excel
(aside from adding the results to a row instead of a column)?

"rafael" wrote:

i have excel 2000 in portuguese PT

"gpmichal" escreveu:

Before I look into this any further, I need to know which version of Excel
you're using. I find it uncommon, at least in my experience, that you're
able to use semi-colons in your formulas instead of commas. Also, when I
tried the SE formula you used, all I got was the #Name error and if I tried
it with semi-colons, I got the formula has an error msg. Furthermore, when I
read what the SE formula is supposed to do, I don't see how you're even using
it for the intended purpose. It says it's supposed to return the number of
the first character in a string that matches the criteria e.g.,

=SEARCH("n","printer")

This function returns 4 because "n" is the fourth character in the word
"printer."

You can also search for words within other words. For example, the function

=SEARCH("base","database")


"rafael" wrote:

, would you rather not delete the rows and just have Col C
list the names consecutively?
yes, i guess that would work - (if i just cant rid of them :)) gotta test it
first, of course

ty again!

"gpmichal" escreveu:

Yes, it can be done. Do you want to delete the entire row if the cell in Col
C is blank? Or, would you rather not delete the rows and just have Col C
list the names consecutively?

"rafael" wrote:

THANKS AGAIN FOR HEELPING

I USED:
=SE(B1="blue";A1;"")

AND IT WORKS, WITH A CATCH:
I NEED TO ELIMINATE ALL THOSE EMPTY CELLS IN MY NEW COLUMN THAT DONT DISPLAY
DATA AT ALL
IS THAT POSSIBLE?

TY AGAIN

sorry caps :((

"gpmichal" escreveu:

Rafael,

You're using semi-colons in your formula instead of commas. Try it after
you make the change.

GP

"rafael" wrote:

ty for helping

i'm using:
=IF(B1="blue";A1;"")

but get a #NAME error, i guess

if instead i use coma, i get a msg error saying formula has an error, which
i cant sort out ... :(

"gpmichal" wrote:

Oh...Ok. Can you simply use the folliwing "IF" formula in the cells in
Column "C"?

=IF(B1="blue",A1,"")

Let me know if that will do. If not, we can write some code to do the same
thing.

GP

"rafael" wrote:

hi

thanks for helping

i'm sorry - i tried your code but i was wrong - what i wanted was names in a
new column, not row ... :(

by the way: how will i control that future column?

ty

"gpmichal" escreveu:

Try this, Rafael,

Option Explicit

Sub ChooseEyeColor()

Dim i As Integer
Dim combo, name As String

i = 1

Do While Range("A" & i) < ""

If Range("B" & i) = "blue" Then
name = Range("A" & i)
combo = combo & name & ", "
End If

i = i + 1

Loop

Range("A4") = combo

End Sub

"rafael" wrote:

imagine i have two rows: name and color:
mary blue
ken brown
will blue

what i want is having, in another row, just the names that have blue eyes,
like
mary
will

thank you

initial case:
a blue
b yhsyh
c ythjtyhts
d blue
f blue
k we
g eerer
h blue
--------------------------------

after first activate:
a blue a
b yhsyh d
c ythjtyhts f
d blue h
f blue
k we
g eerer
h blue
-------------------------------------
after second activate:

a blue a
b yhsyh d
c ythjtyhts f
d blue d
f blue f
k we h
g eerer
h blue
---------------------------------
after third activate:

a blue a
b yhsyh d
c ythjtyhts f
d blue d
f blue f
k we h
g eerer h
h blue
--------------------------------

and so on

so, you see, if i'm changing between sheets, i'll activate a lot

is there a way to fix it?

thanks again :)


"gpmichal" escreveu:

Rafael,

I'm having a little bit of trouble understanding the issue where the last
row is duplicating. When I run the procedure, I get the results below.
Perhaps it has something to do with the Activate event??

Mary blue Mary
tom brown gary
bob black Weasel
gary blue
rafael gray
Weasel blue


"rafael" wrote:

i just tried inserting your code in the activate event, and its perfect for
me, because i have original data in one sheet and the "new" column in another
sheet

the only problem is that everytime it activates, it repeates the very last
ocorrence .... :(((

"rafael" escreveu:

yes, it works nicely, though there's a catch

what i exactly need is a bit diferent: the number of rows (yes, rows) varies
- it means i can open or close a row at any time i want

what i want is having that column with the current opened rows names

so, the code is a macro, and that implies i have to run it everytime i close
or open, or even create a new row

is it possible to have code in a cell to acomplish what your macro does?

also, if i close my book and open it again, add a new row and run the macro,
last "blue" name appears repeated :((

thanks again!

"gpmichal" escreveu:

Well allrighty then, Portuguese it is.

Try this, Rafael. It work fine in Excel 2007:

Option Explicit
Sub Select_Names_by_Color()

Dim i, dataCount As Integer
Dim myRange As Range
Set myRange = Range("A:A")
dataCount = Application.WorksheetFunction.CountA(myRange)

i = 1

Do While Range("A" & i) < ""

If Range("B" & i) = "blue" Then
Range("C" & i) = Range("A" & i)
End If

i = i + 1

Loop

For i = dataCount To 1 Step -1

If Range("C" & i) = "" Then
Range("C" & i).Select
Selection.Delete Shift:=xlUp
End If

Next

End Sub



"rafael" wrote:

not spanish - portuguese! :)

yes the code works ok

"gpmichal" escreveu:

Ahhhh...that explains it. You're probably using a Spanish keyboard. My wife
is from Mexico and I noticed that Spanish keyboards are very different from
American ones. Probably has formulas set up different too.

Any way, I'm using Excel 2007 (USA) so I'm not sure if the code I write will
work in Excel 2000. Did that first VBA code I wrote work in your Excel
(aside from adding the results to a row instead of a column)?

"rafael" wrote:

i have excel 2000 in portuguese PT

"gpmichal" escreveu:

Before I look into this any further, I need to know which version of Excel
you're using. I find it uncommon, at least in my experience, that you're
able to use semi-colons in your formulas instead of commas. Also, when I
tried the SE formula you used, all I got was the #Name error and if I tried
it with semi-colons, I got the formula has an error msg. Furthermore, when I
read what the SE formula is supposed to do, I don't see how you're even using
it for the intended purpose. It says it's supposed to return the number of
the first character in a string that matches the criteria e.g.,

=SEARCH("n","printer")

This function returns 4 because "n" is the fourth character in the word
"printer."

You can also search for words within other words. For example, the function

=SEARCH("base","database")


"rafael" wrote:

, would you rather not delete the rows and just have Col C
list the names consecutively?
yes, i guess that would work - (if i just cant rid of them :)) gotta test it
first, of course

ty again!

"gpmichal" escreveu:

Yes, it can be done. Do you want to delete the entire row if the cell in Col
C is blank? Or, would you rather not delete the rows and just have Col C
list the names consecutively?

"rafael" wrote:

THANKS AGAIN FOR HEELPING

I USED:
=SE(B1="blue";A1;"")

AND IT WORKS, WITH A CATCH:
I NEED TO ELIMINATE ALL THOSE EMPTY CELLS IN MY NEW COLUMN THAT DONT DISPLAY
DATA AT ALL
IS THAT POSSIBLE?

TY AGAIN

sorry caps :((

"gpmichal" escreveu:

Rafael,

You're using semi-colons in your formula instead of commas. Try it after
you make the change.

GP

"rafael" wrote:

ty for helping

i'm using:
=IF(B1="blue";A1;"")

but get a #NAME error, i guess

if instead i use coma, i get a msg error saying formula has an error, which
i cant sort out ... :(

"gpmichal" wrote:

Oh...Ok. Can you simply use the folliwing "IF" formula in the cells in
Column "C"?

=IF(B1="blue",A1,"")

Let me know if that will do. If not, we can write some code to do the same
thing.

GP

"rafael" wrote:

hi

thanks for helping

i'm sorry - i tried your code but i was wrong - what i wanted was names in a
new column, not row ... :(

by the way: how will i control that future column?

ty

"gpmichal" escreveu:

Try this, Rafael,

Option Explicit

Sub ChooseEyeColor()

Dim i As Integer
Dim combo, name As String

i = 1

Do While Range("A" & i) < ""

If Range("B" & i) = "blue" Then
name = Range("A" & i)
combo = combo & name & ", "
End If

i = i + 1

Loop

Range("A4") = combo

End Sub

"rafael" wrote:

imagine i have two rows: name and color:
mary blue
ken brown
will blue

what i want is having, in another row, just the names that have blue eyes,
like
mary
will

thank you

Oh, ok. I see what you mean now. My first thought is that we should delete
the data in Col C at the beginning of the code. That way, every time you
Activate, the code will list the data fresh again.

Try adding Columns (C). ClearContents at the top right below the Dim
statements.

"rafael" wrote:

initial case:
a blue
b yhsyh
c ythjtyhts
d blue
f blue
k we
g eerer
h blue
--------------------------------

after first activate:
a blue a
b yhsyh d
c ythjtyhts f
d blue h
f blue
k we
g eerer
h blue
-------------------------------------
after second activate:

a blue a
b yhsyh d
c ythjtyhts f
d blue d
f blue f
k we h
g eerer
h blue
---------------------------------
after third activate:

a blue a
b yhsyh d
c ythjtyhts f
d blue d
f blue f
k we h
g eerer h
h blue
--------------------------------

and so on

so, you see, if i'm changing between sheets, i'll activate a lot

is there a way to fix it?

thanks again :)


"gpmichal" escreveu:

Rafael,

I'm having a little bit of trouble understanding the issue where the last
row is duplicating. When I run the procedure, I get the results below.
Perhaps it has something to do with the Activate event??

Mary blue Mary
tom brown gary
bob black Weasel
gary blue
rafael gray
Weasel blue


"rafael" wrote:

i just tried inserting your code in the activate event, and its perfect for
me, because i have original data in one sheet and the "new" column in another
sheet

the only problem is that everytime it activates, it repeates the very last
ocorrence .... :(((

"rafael" escreveu:

yes, it works nicely, though there's a catch

what i exactly need is a bit diferent: the number of rows (yes, rows) varies
- it means i can open or close a row at any time i want

what i want is having that column with the current opened rows names

so, the code is a macro, and that implies i have to run it everytime i close
or open, or even create a new row

is it possible to have code in a cell to acomplish what your macro does?

also, if i close my book and open it again, add a new row and run the macro,
last "blue" name appears repeated :((

thanks again!

"gpmichal" escreveu:

Well allrighty then, Portuguese it is.

Try this, Rafael. It work fine in Excel 2007:

Option Explicit
Sub Select_Names_by_Color()

Dim i, dataCount As Integer
Dim myRange As Range
Set myRange = Range("A:A")
dataCount = Application.WorksheetFunction.CountA(myRange)

i = 1

Do While Range("A" & i) < ""

If Range("B" & i) = "blue" Then
Range("C" & i) = Range("A" & i)
End If

i = i + 1

Loop

For i = dataCount To 1 Step -1

If Range("C" & i) = "" Then
Range("C" & i).Select
Selection.Delete Shift:=xlUp
End If

Next

End Sub



"rafael" wrote:

not spanish - portuguese! :)

yes the code works ok

"gpmichal" escreveu:

Ahhhh...that explains it. You're probably using a Spanish keyboard. My wife
is from Mexico and I noticed that Spanish keyboards are very different from
American ones. Probably has formulas set up different too.

Any way, I'm using Excel 2007 (USA) so I'm not sure if the code I write will
work in Excel 2000. Did that first VBA code I wrote work in your Excel
(aside from adding the results to a row instead of a column)?

"rafael" wrote:

i have excel 2000 in portuguese PT

"gpmichal" escreveu:

Before I look into this any further, I need to know which version of Excel
you're using. I find it uncommon, at least in my experience, that you're
able to use semi-colons in your formulas instead of commas. Also, when I
tried the SE formula you used, all I got was the #Name error and if I tried
it with semi-colons, I got the formula has an error msg. Furthermore, when I
read what the SE formula is supposed to do, I don't see how you're even using
it for the intended purpose. It says it's supposed to return the number of
the first character in a string that matches the criteria e.g.,

=SEARCH("n","printer")

This function returns 4 because "n" is the fourth character in the word
"printer."

You can also search for words within other words. For example, the function

=SEARCH("base","database")


"rafael" wrote:

, would you rather not delete the rows and just have Col C
list the names consecutively?
yes, i guess that would work - (if i just cant rid of them :)) gotta test it
first, of course

ty again!

"gpmichal" escreveu:

Yes, it can be done. Do you want to delete the entire row if the cell in Col
C is blank? Or, would you rather not delete the rows and just have Col C
list the names consecutively?

"rafael" wrote:

THANKS AGAIN FOR HEELPING

I USED:
=SE(B1="blue";A1;"")

AND IT WORKS, WITH A CATCH:
I NEED TO ELIMINATE ALL THOSE EMPTY CELLS IN MY NEW COLUMN THAT DONT DISPLAY
DATA AT ALL
IS THAT POSSIBLE?

TY AGAIN

sorry caps :((

"gpmichal" escreveu:

Rafael,

You're using semi-colons in your formula instead of commas. Try it after
you make the change.

GP

"rafael" wrote:

ty for helping

i'm using:
=IF(B1="blue";A1;"")

but get a #NAME error, i guess

if instead i use coma, i get a msg error saying formula has an error, which
i cant sort out ... :(

"gpmichal" wrote:

Oh...Ok. Can you simply use the folliwing "IF" formula in the cells in
Column "C"?

=IF(B1="blue",A1,"")

Let me know if that will do. If not, we can write some code to do the same
thing.

GP

"rafael" wrote:

hi

thanks for helping

i'm sorry - i tried your code but i was wrong - what i wanted was names in a
new column, not row ... :(

by the way: how will i control that future column?

ty

"gpmichal" escreveu:

Try this, Rafael,

Option Explicit

Sub ChooseEyeColor()

Dim i As Integer
Dim combo, name As String

i = 1

Do While Range("A" & i) < ""

If Range("B" & i) = "blue" Then
name = Range("A" & i)
combo = combo & name & ", "
End If

i = i + 1

Loop

Range("A4") = combo

End Sub

"rafael" wrote:

imagine i have two rows: name and color:
mary blue
ken brown
will blue

what i want is having, in another row, just the names that have blue eyes,
like
mary
will

thank you