Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find & Replace: find part cell, replace whole cell | Excel Worksheet Functions | |||
Find and Replace - Replace with Blank Space | Excel Discussion (Misc queries) | |||
where to put results of find operation in find and replace functio | Excel Worksheet Functions | |||
find and replace - replace data in rows to separated by commas | Excel Worksheet Functions | |||
find replace cursor default to find box | Excel Discussion (Misc queries) |