Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 ! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro that delets rows if keyword is found | Excel Programming | |||
Macro that concatenates content from cells if given keyword is found | Excel Programming | |||
Macro analyses Cell.Serches for given word,the puts text in cell a | Excel Programming | |||
Macro - reads cells in a column .If keyword found moves cell conte | Excel Programming | |||
Macro searches for keyword. If found , puts 0 in cell from next co | Excel Programming |