Search and replace

[Terri]

I'm new to programming in Excel and would appreciate any help.

I have a worksheet set up as follows. Column A is a sequence of numbers.
Column B are text strings
1,abc
2,def
3,zxz
etc

In the rest of the spreadsheet are numbers which correspond to the numbers
in column A. These numbers can be in any column besides A and B. I would
like to search all columns in the worksheet besides A and B and replace that
value with the matching concatenated value of column A and B. So if cell D7
is "2", then D7 should become "2-def"

My thanks to anyone who can get me started.

 

[Guest]

Terri:

Learning how to program over the Newsgroup is difficult. I'd like to
have a whiteboard with colored markers to give you tips and pointers. But,
to your question, try something like this:

Sub myReplace()
Dim intCOLA_row As Integer ' runs down column A
Dim intROW As Integer ' runs down all rows for the replace
Dim intCOL As Integer ' runs across all columns for the replace
Dim intLastRow As Integer, intLastCol As Integer ' for the end of our
worksheet

' We'll presume that column A is filled and the first blank cell down column A
' means that we're done.

ActiveCell.SpecialCells(xlLastCell).Select ' selects the last cell on the
worksheet
intLastRow = Selection.Row ' grab the last row number
intLastCol = Selection.Column ' grab the last column number

intCOLA_row = 2 ' presumes that row 1 is some header row, change this as
needed

For intRow = intCOLA_row to intLastRow
For intCol = 3 to intLastCol ' noting that column B is the replace text
string
If Val(Cells(intRow, intCol)) <> 0 Then ' we have a numeric cell
' Write code to run down all of column A to see if we have a
match
' to Cells(intRow, intCol)
For intI = intCOLA_row to intLastRow
' Check the cell in column A against the cell we're
holding
Next intI

' To fill the target cell, use
Cells(intRow, intCol) = Cells(intRow, intCol) & "-" &
Cells(found_row, 2)
Endif
Next intCol
Next intRow
End Sub

Best of luck. Being a good VBA programmer can make you the local hero.

Steve in Ohio