Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find & Replace Macro
Here is my data:
Col 1 Col 2 STDNUMBER IDSTUDENT 27050 1782 27004 1784 27063 1786 27006 1788 Basically what I need to do is look in the entire spreadsheet for (in example 1) 1782 and replace it with 27050, and continue down the list automatically to example 2, find 1784 and replace with 27004 and so on until it runs out of data in these 2 columns. Can anyone help???? Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find & Replace Macro
Sub ABC()
Dim sh as worksheet, sh1 as worksheet Dim rng as Range, cell as Range set sh = Activesheet set rng = sh.Range(sh.Cells(2,1),sh.Cells(2,1).End(xldown)) for each cell in rng for each sh1 in worksheets sh1.cells.Replace What:=cell.offset(0,1).Value, _ Replacement:=cell.Value, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ MatchCase:=False Next sh1 Next cell End Sub Assume that there will be no duplicate values between columns Untested pseudo code, but should get you started. -- Regards, Tom Ogilvy "Rob Wnuk" wrote: Here is my data: Col 1 Col 2 STDNUMBER IDSTUDENT 27050 1782 27004 1784 27063 1786 27006 1788 Basically what I need to do is look in the entire spreadsheet for (in example 1) 1782 and replace it with 27050, and continue down the list automatically to example 2, find 1784 and replace with 27004 and so on until it runs out of data in these 2 columns. Can anyone help???? Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find & Replace Macro
Tom,
I'm not quite sure what this code is supposed to do. I'm not much of a programmer. The code runs, and replaces numbers with the number 1, but it doesn't seem to have a pattern. ANy help would be appreciated. "Tom Ogilvy" wrote: Sub ABC() Dim sh as worksheet, sh1 as worksheet Dim rng as Range, cell as Range set sh = Activesheet set rng = sh.Range(sh.Cells(2,1),sh.Cells(2,1).End(xldown)) for each cell in rng for each sh1 in worksheets sh1.cells.Replace What:=cell.offset(0,1).Value, _ Replacement:=cell.Value, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ MatchCase:=False Next sh1 Next cell End Sub Assume that there will be no duplicate values between columns Untested pseudo code, but should get you started. -- Regards, Tom Ogilvy "Rob Wnuk" wrote: Here is my data: Col 1 Col 2 STDNUMBER IDSTUDENT 27050 1782 27004 1784 27063 1786 27006 1788 Basically what I need to do is look in the entire spreadsheet for (in example 1) 1782 and replace it with 27050, and continue down the list automatically to example 2, find 1784 and replace with 27004 and so on until it runs out of data in these 2 columns. Can anyone help???? Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find & Replace Macro
I interpret entire spreadsheet to mean multiple worksheets in a workbook. I
have added code to restrict changes so they won't occur on the sheet that contain the two columns of data. Sub ABC() Dim sh As Worksheet, sh1 As Worksheet Dim rng As Range, cell As Range Set sh = ActiveSheet Set rng = sh.Range(sh.Cells(2, 1), sh.Cells(2, 1).End(xlDown)) For Each cell In rng For Each sh1 In Worksheets If sh.Name < sh1.Name Then sh1.Cells.Replace What:=cell.Offset(0, 1).Value, _ Replacement:=cell.Value, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ MatchCase:=False End If Next sh1 Next cell End Sub worked for me as you describe. The previous code worked as well, but changed the original list. If it is just one sheet, and changes are in columns C to IV Sub ABC() Dim sh As Worksheet Dim rng As Range, cell as Range Set sh = ActiveSheet Set rng = sh.Range(sh.Cells(2, 1), sh.Cells(2, 1).End(xlDown)) for each cell in rng sh.Range("C:IV").Replace What:=cell.Offset(0, 1).Value, _ Replacement:=cell.Value, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ MatchCase:=False Next End Sub Both macros assume the list is in columns A and B of the active sheet with the first paring starting in row 2. -- Regards, Tom Ogilvy "Rob Wnuk" wrote in message ... Tom, I'm not quite sure what this code is supposed to do. I'm not much of a programmer. The code runs, and replaces numbers with the number 1, but it doesn't seem to have a pattern. ANy help would be appreciated. "Tom Ogilvy" wrote: Sub ABC() Dim sh as worksheet, sh1 as worksheet Dim rng as Range, cell as Range set sh = Activesheet set rng = sh.Range(sh.Cells(2,1),sh.Cells(2,1).End(xldown)) for each cell in rng for each sh1 in worksheets sh1.cells.Replace What:=cell.offset(0,1).Value, _ Replacement:=cell.Value, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ MatchCase:=False Next sh1 Next cell End Sub Assume that there will be no duplicate values between columns Untested pseudo code, but should get you started. -- Regards, Tom Ogilvy "Rob Wnuk" wrote: Here is my data: Col 1 Col 2 STDNUMBER IDSTUDENT 27050 1782 27004 1784 27063 1786 27006 1788 Basically what I need to do is look in the entire spreadsheet for (in example 1) 1782 and replace it with 27050, and continue down the list automatically to example 2, find 1784 and replace with 27004 and so on until it runs out of data in these 2 columns. Can anyone help???? Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro: Find and replace | Excel Discussion (Misc queries) | |||
Find and Replace Macro | Excel Programming | |||
Using Find and Replace to replace " in a macro | Excel Programming | |||
find replace macro | Excel Programming | |||
find and replace macro need help | Excel Programming |