MS Excel Macro - insert text based on search

May 13, 2010 at 13:44:37
Specs: Windows XP
I need to create a macro that will search column A for a text string, and then insert the data in cell A1 in column C of the same row.

Basically, search column A for text "String10", if found, insert contents of cell A1 in column C of the same row.

Any help would be greatly appreciated!


See More: MS Excel Macro - insert text based on search

Report •


#1
May 13, 2010 at 20:23:01
Hi,
Following is the subroutine code for your query:
'==============================
Sub findAndWrite()
Range("a1").Select
Do Until ActiveCell.Value = ""
If ActiveCell.Value = "String10" Then
Cells(ActiveCell.Row, 3).Value = Range("a1").Value
'comment:you write in the cell with C column of the same row
End If
ActiveCell.Offset(1, 0).Select
'comment:you go to the next cell
Loop
End Sub
'=======================
Be Happy and make others Happy
viswam

Report •

#2
May 14, 2010 at 07:23:28
Hello Viswam,
The one problem I have is that column A is blank except for the rows that have String10 in them, so the search criteria that ends the macro occurs immediately before checking all of the rows. If I populate all the blank cells in column A, it works perfectly.

Thanks again,

James


Report •

#3
May 14, 2010 at 09:23:55
Hi,

From what you say, I am not sure that you need a macro.

If A1 contains text to go in column C and B1 contains the text to find in column A,
then in cell C2 enter this formula:

=IF(A2=$B$1,$A$1,"")
Drag the formula down in column C alongside the cells containing text in column A.

In this example, if cell A1 contains "Test" and B1 contains "String10" then any cell in column C on the same row as "String10" in a cell in column A, will contain "Test"

Regards


Report •

Related Solutions

#4
May 14, 2010 at 10:10:55
Hello Humar,

Thanks, and that would work, butI need to use the sheet to import the data into another tool, so the formula's won't work since I need the text in the column. The file will be converted to .csv format.

Thanks,

James


Report •

#5
May 14, 2010 at 11:46:28
Almost verbatim from the VBA Help file for the Find Method:

Sub Find_and_Write()
  With Worksheets(1).Range("a1:a500")
    Set c = .Find("String10", LookIn:=xlValues)
    If Not c Is Nothing Then
        firstAddress = c.Address
        Do
            Cells(c.Row, 3) = Cells(1)
            Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> firstAddress
    End If
  End With
End Sub


Report •

#6
May 14, 2010 at 11:54:02
That worked perfectly, thanks!

Report •

#7
May 14, 2010 at 11:57:10
Anytime

Report •

#8
May 14, 2010 at 12:02:17
One quick comment, this is the first time I have been on this site, came across it kind of by accident and I have to say, the help and the quick response has made this go right to the top of my list. When they invented the interent, this is what they had in mind. Thanks to everyone who responded.

Report •

#9
May 14, 2010 at 12:04:46
Hi,

when you save it as a csv file, the formulas will be removed and replaced with the text.

Try it and see what happens. I saved it as a csv file then opened it as a text file (notepad) and in Excel and in both cases - no formulas - just the text.

Regards


Report •


Ask Question