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.