Using find and replace

[Terri]

I'm reposting this. Hopefully I can state my issue more clearly and someone
can get me started.

In column A I have a list of ordered numbers. In column B is text which
corresponds to column A. The rest of the worksheet C:Z contains numbers
which match a value column A. These numbers can be in any cell within this
range C:Z.

I would like to loop through all the values in column A until I reach an
empty cell. For each value in column A I want to search(find) columns C:Z
for the matching number and the replace the contents of the cell in D:Z with
the concatenated value of columns A and B(seperated by a dash-).

Here's some sample data and my expected result.
A1=1
A2=2
A3=3
B1=abc
B2=def
B3= ghi
C13=2
F29=1
M3=3

After the process:
C13=2-def
F29=1-abc
M3=3-ghi

Thanks to anyone who could help

 

[Tom Ogilvy]

Dim rng1 as Range, rng2 as Range, cell as Range
Dim cell1 as Range
set rng1 = Range(cells(1,1),Cells(rows.count,1).End(xlup))
set rng2 = rng1.offset(0,2).Resize(,24)
for each cell in rng1
set cell1 = rng2.Find(cell)
if not cell1 is nothing then
cell1.Value = cell.Value & "-" & cell.offset(0,1).Value
end if
Next

 

[Terri]

Dim rng1 as Range, rng2 as Range, cell as Range

Dim cell1 as Range
set rng1 = Range(cells(1,1),Cells(rows.count,1).End(xlup))
set rng2 = rng1.offset(0,2).Resize(,24)
for each cell in rng1
set cell1 = rng2.Find(cell)
if not cell1 is nothing then
cell1.Value = cell.Value & "-" & cell.offset(0,1).Value
end if
Next

Thanks Tom, That more than gets me started. I trying to understand what your
code does but I am getting unexpected results

Set A1-A12 to 1-12
Set B1-B12 to January to December
Set C1-C12 to 1-12

There's some issue with A1 and A10 which may be the cell formats.

Furthermore if you use the above sample data and then set Set D1-D12 to 1-12
you'll see that the find function will find the first occurrence only, it
won't find all occurrences.

Thanks again

 

[Tom Ogilvy]

Sub AABB()
Dim rng1 As Range, rng2 As Range, cell As Range
Dim cell1 As Range, sAddr As String
Set rng1 = Range(Cells(1, 1), Cells(Rows.Count, 1).End(xlUp))
Set rng2 = rng1.Offset(0, 2).Resize(, 24)
For Each cell In rng1
Set cell1 = rng2.Find(What:=cell, _
LookIn:=xlValues, LookAt:=xlWhole)
If Not cell1 Is Nothing Then
sAddr = cell1.Address
Do
cell1.Value = "'" & cell.Value & "-" & cell.Offset(0, 1).Value
Set cell1 = rng2.FindNext(cell1)
Loop While Not cell1 Is Nothing
End If
Next

End Sub