Macro puts text if keyword is found in cell above
 

I have a sheet . in A column i have cells with text , and empty cells .
I want the macro to search the column for given keyword . If found , to
go to next cell in A column . If that cell in empty , it should put "No
description" . If that cell is not empty , it should leave it as it is .
Example :

keyword : mother

A1: mother goes home
A2: house
A3: empty
A4: my mother is ...
A5: empty

So , A5 should become : "No description"

Thanks !


--
andrei
------------------------------------------------------------------------
andrei's Profile: http://www.thecodecage.com/forumz/member.php?u=1056
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=201969

http://www.thecodecage.com/forumz

Macro puts text if keyword is found in cell above
 

Give this code a try (it will ask you for the word to search for and then do
as you asked)...

Sub InsertNoDescriptions()
Dim SearchWord As String, Cell As Range, Blanks As Range
SearchWord = InputBox("Enter word to find...")
If SearchWord = "" Then Exit Sub
On Error Resume Next
Set Blanks = Range("A2:A" & Rows.Count).SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If Blanks Is Nothing Then Exit Sub
For Each Cell In Blanks
If InStr(1, Cell.Offset(-1).Value, SearchWord, vbTextCompare) Then
Cell.Value = "No description"
End If
Next
End Sub

--
Rick (MVP - Excel)



"andrei" wrote in message
...

I have a sheet . in A column i have cells with text , and empty cells .
I want the macro to search the column for given keyword . If found , to
go to next cell in A column . If that cell in empty , it should put "No
description" . If that cell is not empty , it should leave it as it is .
Example :

keyword : mother

A1: mother goes home
A2: house
A3: empty
A4: my mother is ...
A5: empty

So , A5 should become : "No description"

Thanks !


--
andrei
------------------------------------------------------------------------
andrei's Profile: http://www.thecodecage.com/forumz/member.php?u=1056
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=201969

http://www.thecodecage.com/forumz

Macro puts text if keyword is found in cell above
 

Sub findthings()
whatword = InputBox("Enter a Word")
With ActiveSheet.Columns(1)
Set c = .Find(whatword, LookIn:=xlValues, lookat:=xlPart)
If Not c Is Nothing Then
FirstAddress = c.Address
Do
With c.Offset(1, 0)
If .Value = "" Then
.Value = "No Description"
End If
End With
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < FirstAddress
End If
End With
End Sub


Gord Dibben MS Excel MVP

On Tue, 11 May 2010 16:34:24 +0100, andrei
wrote:


I have a sheet . in A column i have cells with text , and empty cells .
I want the macro to search the column for given keyword . If found , to
go to next cell in A column . If that cell in empty , it should put "No
description" . If that cell is not empty , it should leave it as it is .
Example :

keyword : mother

A1: mother goes home
A2: house
A3: empty
A4: my mother is ...
A5: empty

So , A5 should become : "No description"

Thanks !

Macro puts text if keyword is found in cell above
 

thanks guys !


--
andrei
------------------------------------------------------------------------
andrei's Profile: http://www.thecodecage.com/forumz/member.php?u=1056
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=201969

http://www.thecodecage.com/forumz