Macro puts text if keyword is found in cell above

[andrei]

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 , t
go to next cell in A column . If that cell in empty , it should put "N
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 !

 

[Rick Rothstein]

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

 

[Gord Dibben]

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

 

[andrei]

thanks guys !