Using IF to locate specific text string value

[Guest]

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.

 

[Guest]

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

 

[Guest]

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

sebastien

 

[Guest]

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.

 

[Tom Ogilvy]

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

 

[Guest]

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.

 

[Guest]

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

 

[Tom Ogilvy]

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

 

[Guest]

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

 

[Myrna Larson]

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.