Using find and replace
 

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

Using find and replace
 

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

--
Regards,
Tom Ogilvy


"Terri" wrote in message
...
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

Using find and replace
 

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

Using find and replace
 

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

--
Regards,
Tom Ogilvy

"Terri" wrote in message
...
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