Previous Topic Next Topic

Search a word in the column

Closed
Nalini - Sep 15, 2011 at 06:37 PM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Sep 16, 2011 at 12:22 AM
Hello,

In Excel sheet,i wanted to Search a word in the column if it founds in the excel sheet then it should update word as "exempt" in the adjacent cell.

Please help me on this
Related:

1 response

Answer 1 / 1
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 811
Sep 16, 2011 at 12:22 AM
try this macro

there are two input boxes one for the word and one for t he relevant column number 

Sub test() 
Dim j As Long, x As String, cfind As Range, add As String 
x = InputBox("type the word you want to find") 
j = InputBox("the column no. where the word appearss") 
With Columns(j) 
Set cfind = .Cells.Find(what:=x, lookat:=xlWhole) 
If Not cfind Is Nothing Then 
add = cfind.Address 
cfind.Offset(0, 1) = "exempt" 
End If 
Do 
Set cfind = .Cells.FindNext(cfind) 
If cfind Is Nothing Then Exit Do 
If cfind.Address = add Then Exit Do 
cfind.Offset(0, 1) = "exempt" 
Loop 
End With 
End Sub
0