I would like to use a macro to find in a spreadsheet all specific text string
values and then change the left adjacent cell value to display "N/A". While
leaving the specific value alone.

There are approximately 9 string values. n=#.

I need to use this function alot so I would like to add it to the
personal.xls for all the workbooks using this worksheet.

Do you want NA when the string exists? or when it deosn't exist?
In the left cell, say A2:

1. Using VHLOOKUP:
-- if count of the string is greater than zero then NA else "ok"
=IF ( ISNA(VLOOKUP("Hello" , B2:H2 , 1, FALSE)), "doesn't exist", "exist")

2. Using Countif
= IF( COUNTIF(B2:B10,"Hello")0, "exist", "does not exist" )

Copy/paste down along the data.
Regards,
Sebastien
"Metalmaiden" wrote:

I would like to use a macro to find in a spreadsheet all specific text string
values and then change the left adjacent cell value to display "N/A". While
leaving the specific value alone.

There are approximately 9 string values. n=#.

I need to use this function alot so I would like to add it to the
personal.xls for all the workbooks using this worksheet.

sorry, in case 2, meant B2:H2:
= IF( COUNTIF(B2:H2,"Hello")0, "exist", "does not exist" )

sebastien
"sebastienm" wrote:

Do you want NA when the string exists? or when it deosn't exist?
In the left cell, say A2:

1. Using VHLOOKUP:
-- if count of the string is greater than zero then NA else "ok"
=IF ( ISNA(VLOOKUP("Hello" , B2:H2 , 1, FALSE)), "doesn't exist", "exist")

2. Using Countif
= IF( COUNTIF(B2:B10,"Hello")0, "exist", "does not exist" )

Copy/paste down along the data.
Regards,
Sebastien
"Metalmaiden" wrote:

I would like to use a macro to find in a spreadsheet all specific text string
values and then change the left adjacent cell value to display "N/A". While
leaving the specific value alone.

There are approximately 9 string values. n=#.

I need to use this function alot so I would like to add it to the
personal.xls for all the workbooks using this worksheet.

Sorry for being so confusing, cut and paste will not work as those cells are
already populated. So I cannot place a formula in there or I will lose data I
wish to retain.

I want NA when the string exists "n=#" where # could equal any number. and
the adjacent cell which already has a value I want to have NA overwrite that
value. While leaving all other values in place.

It is a response report and if there are not sufficent responses we want to
remove that response data in the adjacent cell and replace it with NA. Since
it is not meaningful or statistically significant.

What I would like to do is place it in a macro because this is outputted
data and already completely formatted.

"sebastienm" wrote:

Do you want NA when the string exists? or when it deosn't exist?
In the left cell, say A2:

1. Using VHLOOKUP:
-- if count of the string is greater than zero then NA else "ok"
=IF ( ISNA(VLOOKUP("Hello" , B2:H2 , 1, FALSE)), "doesn't exist", "exist")

2. Using Countif
= IF( COUNTIF(B2:B10,"Hello")0, "exist", "does not exist" )

Copy/paste down along the data.
Regards,
Sebastien
"Metalmaiden" wrote:

I would like to use a macro to find in a spreadsheet all specific text string
values and then change the left adjacent cell value to display "N/A". While
leaving the specific value alone.

There are approximately 9 string values. n=#.

I need to use this function alot so I would like to add it to the
personal.xls for all the workbooks using this worksheet.

Sub MarkwithNA()
Dim rng as Range
set rng = Cells.Find("n=#", _
Lookin:=xlValues, lookat:=xlPart)
if not rng is nothing then
fAddr = rng.Address
do
rng.offset(0,-1) = "NA"
set rng = cells.findnext(rng)
loop while rng.Address < fAddr
End if
End Sub

--
Regards,
Tom Ogilvy



"Metalmaiden" wrote in message
...
Sorry for being so confusing, cut and paste will not work as those cells
are
already populated. So I cannot place a formula in there or I will lose
data I
wish to retain.

I want NA when the string exists "n=#" where # could equal any number. and
the adjacent cell which already has a value I want to have NA overwrite
that
value. While leaving all other values in place.

It is a response report and if there are not sufficent responses we want
to
remove that response data in the adjacent cell and replace it with NA.
Since
it is not meaningful or statistically significant.

What I would like to do is place it in a macro because this is outputted
data and already completely formatted.

"sebastienm" wrote:

Do you want NA when the string exists? or when it deosn't exist?
In the left cell, say A2:

1. Using VHLOOKUP:
-- if count of the string is greater than zero then NA else "ok"
=IF ( ISNA(VLOOKUP("Hello" , B2:H2 , 1, FALSE)), "doesn't exist",
"exist")

2. Using Countif
= IF( COUNTIF(B2:B10,"Hello")0, "exist", "does not exist" )

Copy/paste down along the data.
Regards,
Sebastien
"Metalmaiden" wrote:

I would like to use a macro to find in a spreadsheet all specific text
string
values and then change the left adjacent cell value to display "N/A".
While
leaving the specific value alone.

There are approximately 9 string values. n=#.

I need to use this function alot so I would like to add it to the
personal.xls for all the workbooks using this worksheet.

This partially works, but it also takes all values starting with one for some
reason and marks them "NA" such as n=11 n=19 etc.

"Tom Ogilvy" wrote:

Sub MarkwithNA()
Dim rng as Range
set rng = Cells.Find("n=#", _
Lookin:=xlValues, lookat:=xlPart)
if not rng is nothing then
fAddr = rng.Address
do
rng.offset(0,-1) = "NA"
set rng = cells.findnext(rng)
loop while rng.Address < fAddr
End if
End Sub

--
Regards,
Tom Ogilvy



"Metalmaiden" wrote in message
...
Sorry for being so confusing, cut and paste will not work as those cells
are
already populated. So I cannot place a formula in there or I will lose
data I
wish to retain.

I want NA when the string exists "n=#" where # could equal any number. and
the adjacent cell which already has a value I want to have NA overwrite
that
value. While leaving all other values in place.

It is a response report and if there are not sufficent responses we want
to
remove that response data in the adjacent cell and replace it with NA.
Since
it is not meaningful or statistically significant.

What I would like to do is place it in a macro because this is outputted
data and already completely formatted.

"sebastienm" wrote:

Do you want NA when the string exists? or when it deosn't exist?
In the left cell, say A2:

1. Using VHLOOKUP:
-- if count of the string is greater than zero then NA else "ok"
=IF ( ISNA(VLOOKUP("Hello" , B2:H2 , 1, FALSE)), "doesn't exist",
"exist")

2. Using Countif
= IF( COUNTIF(B2:B10,"Hello")0, "exist", "does not exist" )

Copy/paste down along the data.
Regards,
Sebastien
"Metalmaiden" wrote:

I would like to use a macro to find in a spreadsheet all specific text
string
values and then change the left adjacent cell value to display "N/A".
While
leaving the specific value alone.

There are approximately 9 string values. n=#.

I need to use this function alot so I would like to add it to the
personal.xls for all the workbooks using this worksheet.

I figured it out its the xlPart should be xlWhole for a specific string.

"Metalmaiden" wrote:

This partially works, but it also takes all values starting with one for some
reason and marks them "NA" such as n=11 n=19 etc.

"Tom Ogilvy" wrote:

Sub MarkwithNA()
Dim rng as Range
set rng = Cells.Find("n=#", _
Lookin:=xlValues, lookat:=xlPart)
if not rng is nothing then
fAddr = rng.Address
do
rng.offset(0,-1) = "NA"
set rng = cells.findnext(rng)
loop while rng.Address < fAddr
End if
End Sub

--
Regards,
Tom Ogilvy



"Metalmaiden" wrote in message
...
Sorry for being so confusing, cut and paste will not work as those cells
are
already populated. So I cannot place a formula in there or I will lose
data I
wish to retain.

I want NA when the string exists "n=#" where # could equal any number. and
the adjacent cell which already has a value I want to have NA overwrite
that
value. While leaving all other values in place.

It is a response report and if there are not sufficent responses we want
to
remove that response data in the adjacent cell and replace it with NA.
Since
it is not meaningful or statistically significant.

What I would like to do is place it in a macro because this is outputted
data and already completely formatted.

"sebastienm" wrote:

Do you want NA when the string exists? or when it deosn't exist?
In the left cell, say A2:

1. Using VHLOOKUP:
-- if count of the string is greater than zero then NA else "ok"
=IF ( ISNA(VLOOKUP("Hello" , B2:H2 , 1, FALSE)), "doesn't exist",
"exist")

2. Using Countif
= IF( COUNTIF(B2:B10,"Hello")0, "exist", "does not exist" )

Copy/paste down along the data.
Regards,
Sebastien
"Metalmaiden" wrote:

I would like to use a macro to find in a spreadsheet all specific text
string
values and then change the left adjacent cell value to display "N/A".
While
leaving the specific value alone.

There are approximately 9 string values. n=#.

I need to use this function alot so I would like to add it to the
personal.xls for all the workbooks using this worksheet.

Sub MarkwithNA()
Dim rng as Range
Dim fAddr as String
for i = 1 to 9
set rng = Nothing
set rng = Cells.Find("n=" & i, _
Lookin:=xlValues, lookat:=xlWhole)
if not rng is nothing then
fAddr = rng.Address
do
rng.offset(0,-1) = "NA"
set rng = cells.findnext(rng)
loop while rng.Address < fAddr
End if
Next
End Sub


--
Regards,
Tom Ogilvy



"Metalmaiden" wrote in message
...
This partially works, but it also takes all values starting with one for
some
reason and marks them "NA" such as n=11 n=19 etc.

"Tom Ogilvy" wrote:

Sub MarkwithNA()
Dim rng as Range
set rng = Cells.Find("n=#", _
Lookin:=xlValues, lookat:=xlPart)
if not rng is nothing then
fAddr = rng.Address
do
rng.offset(0,-1) = "NA"
set rng = cells.findnext(rng)
loop while rng.Address < fAddr
End if
End Sub

--
Regards,
Tom Ogilvy



"Metalmaiden" wrote in message
...
Sorry for being so confusing, cut and paste will not work as those
cells
are
already populated. So I cannot place a formula in there or I will lose
data I
wish to retain.

I want NA when the string exists "n=#" where # could equal any number.
and
the adjacent cell which already has a value I want to have NA
overwrite
that
value. While leaving all other values in place.

It is a response report and if there are not sufficent responses we
want
to
remove that response data in the adjacent cell and replace it with NA.
Since
it is not meaningful or statistically significant.

What I would like to do is place it in a macro because this is
outputted
data and already completely formatted.

"sebastienm" wrote:

Do you want NA when the string exists? or when it deosn't exist?
In the left cell, say A2:

1. Using VHLOOKUP:
-- if count of the string is greater than zero then NA else "ok"
=IF ( ISNA(VLOOKUP("Hello" , B2:H2 , 1, FALSE)), "doesn't exist",
"exist")

2. Using Countif
= IF( COUNTIF(B2:B10,"Hello")0, "exist", "does not exist" )

Copy/paste down along the data.
Regards,
Sebastien
"Metalmaiden" wrote:

I would like to use a macro to find in a spreadsheet all specific
text
string
values and then change the left adjacent cell value to display
"N/A".
While
leaving the specific value alone.

There are approximately 9 string values. n=#.

I need to use this function alot so I would like to add it to the
personal.xls for all the workbooks using this worksheet.

Thanks for the response this is very elegant and reduces redundancy!!!

"Tom Ogilvy" wrote:

Sub MarkwithNA()
Dim rng as Range
Dim fAddr as String
for i = 1 to 9
set rng = Nothing
set rng = Cells.Find("n=" & i, _
Lookin:=xlValues, lookat:=xlWhole)
if not rng is nothing then
fAddr = rng.Address
do
rng.offset(0,-1) = "NA"
set rng = cells.findnext(rng)
loop while rng.Address < fAddr
End if
Next
End Sub


--
Regards,
Tom Ogilvy



"Metalmaiden" wrote in message
...
This partially works, but it also takes all values starting with one for
some
reason and marks them "NA" such as n=11 n=19 etc.

"Tom Ogilvy" wrote:

Sub MarkwithNA()
Dim rng as Range
set rng = Cells.Find("n=#", _
Lookin:=xlValues, lookat:=xlPart)
if not rng is nothing then
fAddr = rng.Address
do
rng.offset(0,-1) = "NA"
set rng = cells.findnext(rng)
loop while rng.Address < fAddr
End if
End Sub

--
Regards,
Tom Ogilvy



"Metalmaiden" wrote in message
...
Sorry for being so confusing, cut and paste will not work as those
cells
are
already populated. So I cannot place a formula in there or I will lose
data I
wish to retain.

I want NA when the string exists "n=#" where # could equal any number.
and
the adjacent cell which already has a value I want to have NA
overwrite
that
value. While leaving all other values in place.

It is a response report and if there are not sufficent responses we
want
to
remove that response data in the adjacent cell and replace it with NA.
Since
it is not meaningful or statistically significant.

What I would like to do is place it in a macro because this is
outputted
data and already completely formatted.

"sebastienm" wrote:

Do you want NA when the string exists? or when it deosn't exist?
In the left cell, say A2:

1. Using VHLOOKUP:
-- if count of the string is greater than zero then NA else "ok"
=IF ( ISNA(VLOOKUP("Hello" , B2:H2 , 1, FALSE)), "doesn't exist",
"exist")

2. Using Countif
= IF( COUNTIF(B2:B10,"Hello")0, "exist", "does not exist" )

Copy/paste down along the data.
Regards,
Sebastien
"Metalmaiden" wrote:

I would like to use a macro to find in a spreadsheet all specific
text
string
values and then change the left adjacent cell value to display
"N/A".
While
leaving the specific value alone.

There are approximately 9 string values. n=#.

I need to use this function alot so I would like to add it to the
personal.xls for all the workbooks using this worksheet.

The difference between the two is whether you want to find the string ONLY if
that's all that's in the cell, or if it's embedded within a longer string.

On Sat, 9 Oct 2004 15:23:01 -0700, "Metalmaiden"
wrote:

I figured it out its the xlPart should be xlWhole for a specific string.

"Metalmaiden" wrote:

This partially works, but it also takes all values starting with one for
some
reason and marks them "NA" such as n=11 n=19 etc.

"Tom Ogilvy" wrote:

Sub MarkwithNA()
Dim rng as Range
set rng = Cells.Find("n=#", _
Lookin:=xlValues, lookat:=xlPart)
if not rng is nothing then
fAddr = rng.Address
do
rng.offset(0,-1) = "NA"
set rng = cells.findnext(rng)
loop while rng.Address < fAddr
End if
End Sub

--
Regards,
Tom Ogilvy



"Metalmaiden" wrote in message
...
Sorry for being so confusing, cut and paste will not work as those
cells
are
already populated. So I cannot place a formula in there or I will lose
data I
wish to retain.

I want NA when the string exists "n=#" where # could equal any number.
and
the adjacent cell which already has a value I want to have NA overwrite
that
value. While leaving all other values in place.

It is a response report and if there are not sufficent responses we
want
to
remove that response data in the adjacent cell and replace it with NA.
Since
it is not meaningful or statistically significant.

What I would like to do is place it in a macro because this is
outputted
data and already completely formatted.

"sebastienm" wrote:

Do you want NA when the string exists? or when it deosn't exist?
In the left cell, say A2:

1. Using VHLOOKUP:
-- if count of the string is greater than zero then NA else "ok"
=IF ( ISNA(VLOOKUP("Hello" , B2:H2 , 1, FALSE)), "doesn't exist",
"exist")

2. Using Countif
= IF( COUNTIF(B2:B10,"Hello")0, "exist", "does not exist" )

Copy/paste down along the data.
Regards,
Sebastien
"Metalmaiden" wrote:

I would like to use a macro to find in a spreadsheet all specific
text
string
values and then change the left adjacent cell value to display
"N/A".
While
leaving the specific value alone.

There are approximately 9 string values. n=#.

I need to use this function alot so I would like to add it to the
personal.xls for all the workbooks using this worksheet.