Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
A macro solution - replace text given the values
Hi,
I would like to have a macro to replace text in a sheet based on values in another sheet. Sample file is at: http://www.savefile.com/files/1777260 Thx. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
A macro solution - replace text given the values
Sub ReplaceData()
Dim cell As Range Dim res As Variant With ActiveSheet For Each cell In .UsedRange On Error Resume Next res = Application.VLookup(cell.Value, Worksheets("Temp").Columns("A:B"), 2, False) On Error GoTo 0 If Not IsError(res) Then cell.Value = res Next cell End With End Sub -- __________________________________ HTH Bob "Sinner" wrote in message ... Hi, I would like to have a macro to replace text in a sheet based on values in another sheet. Sample file is at: http://www.savefile.com/files/1777260 Thx. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
A macro solution - replace text given the values
Hi,
Right click sheet 1 tab, view code and paste this in and run it Sub standard() For Each c In ActiveSheet.UsedRange On Error Resume Next If Not IsEmpty(c) Then c.Value = WorksheetFunction.VLookup(c.Value, Sheets("Temp").Range("A1:B4"), 2, False) End If Next End Sub Mike "Sinner" wrote: Hi, I would like to have a macro to replace text in a sheet based on values in another sheet. Sample file is at: http://www.savefile.com/files/1777260 Thx. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
A macro solution - replace text given the values
On Sep 9, 4:09*pm, Mike H wrote:
Hi, Right click sheet 1 tab, view code and paste this in and run it Sub standard() For Each c In ActiveSheet.UsedRange * * On Error Resume Next * * If Not IsEmpty(c) Then c.Value = WorksheetFunction.VLookup(c.Value, Sheets("Temp").Range("A1:B4"), 2, False) * * End If Next End Sub Mike "Sinner" wrote: Hi, I would like to have a macro to replace text in a sheet based on values in another sheet. Sample file is at: http://www.savefile.com/files/1777260 Thx.- Hide quoted text - - Show quoted text - Thx bob & mike : ) Mike can u go through your code once again. It seems something is missing. Thx. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
A macro solution - replace text given the values
Hi,
Nothing is missing that i'm aware of. It simply loops through every cell in the used range and if the cell contains a value it does a vlookup of that value on the temp sheet. If it finds a match it populates that value in the cell and if it doesn't it resumes next because of the error generated. The only potential problem I can see is this line has line wrapped when posted and is really a single line. c.Value = WorksheetFunction.VLookup(c.Value, Sheets("Temp").Range("A1:B4"), 2, False) What problem are you getting? Mike "Sinner" wrote: On Sep 9, 4:09 pm, Mike H wrote: Hi, Right click sheet 1 tab, view code and paste this in and run it Sub standard() For Each c In ActiveSheet.UsedRange On Error Resume Next If Not IsEmpty(c) Then c.Value = WorksheetFunction.VLookup(c.Value, Sheets("Temp").Range("A1:B4"), 2, False) End If Next End Sub Mike "Sinner" wrote: Hi, I would like to have a macro to replace text in a sheet based on values in another sheet. Sample file is at: http://www.savefile.com/files/1777260 Thx.- Hide quoted text - - Show quoted text - Thx bob & mike : ) Mike can u go through your code once again. It seems something is missing. Thx. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
A macro solution - replace text given the values
On Sep 9, 6:37*pm, Mike H wrote:
Hi, Nothing is missing that i'm aware of. It simply loops through every cell in the used range and if the cell contains a value it does a vlookup of that value on the temp sheet. If it finds a match it populates that value in the cell and if it doesn't it resumes next because of the error generated. The only potential problem I can see is this line has line wrapped when posted and is really a single line. c.Value = WorksheetFunction.VLookup(c.Value, Sheets("Temp").Range("A1:B4"), 2, False) What problem are you getting? Mike "Sinner" wrote: On Sep 9, 4:09 pm, Mike H wrote: Hi, Right click sheet 1 tab, view code and paste this in and run it Sub standard() For Each c In ActiveSheet.UsedRange * * On Error Resume Next * * If Not IsEmpty(c) Then c.Value = WorksheetFunction.VLookup(c.Value, Sheets("Temp").Range("A1:B4"), 2, False) * * End If Next End Sub Mike "Sinner" wrote: Hi, I would like to have a macro to replace text in a sheet based on values in another sheet. Sample file is at: http://www.savefile.com/files/1777260 Thx.- Hide quoted text - - Show quoted text - Thx bob & mike : ) Mike can u go through your code once again. It seems something is missing. Thx.- Hide quoted text - - Show quoted text - Dear Mike, Both are wokring but now the problem is that I have a lot of data & it's taking too much time. Vlooup calculation time is wayyyy too long. Find/Replace is much faster but can we get it through VB? Thx. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
A macro solution - replace text given the values
Sub way()
Dim strNm As String Dim rngData As Range Dim cel As Range Dim rngReplacement As Range Dim strReplacement As String 'change to the range where your data is Set rngData = Sheet1.UsedRange 'change to the range where your conversion table is Set rngReplacement = Sheet2.UsedRange For Each cel In rngData strNm = cel.Value On Error Resume Next If cel.Value < "" Then strReplacement = _ rngReplacement.Find( _ What:=strNm, _ After:=Sheet2.Range("a1"), _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=True, _ SearchFormat:=False) _ .Offset(0, 1).Value If strReplacement < "" Then _ cel.Replace _ What:=strNm, _ replacement:=strReplacement, _ LookAt:=xlWhole, _ SearchOrder:=xlByRows, _ MatchCase:=True, _ SearchFormat:=False, _ ReplaceFormat:=False End If Next cel On Error GoTo 0 End Sub Cliff Edwards |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
A macro solution - replace text given the values
On Sep 9, 10:36*pm, ward376 wrote:
Sub way() Dim strNm As String Dim rngData As Range Dim cel As Range Dim rngReplacement As Range Dim strReplacement As String 'change to the range where your data is Set rngData = Sheet1.UsedRange 'change to the range where your conversion table is Set rngReplacement = Sheet2.UsedRange For Each cel In rngData strNm = cel.Value On Error Resume Next If cel.Value < "" Then * * strReplacement = _ * * * * rngReplacement.Find( _ * * * * What:=strNm, _ * * * * After:=Sheet2.Range("a1"), _ * * * * LookIn:=xlFormulas, _ * * * * LookAt:=xlWhole, _ * * * * SearchOrder:=xlByRows, _ * * * * SearchDirection:=xlNext, _ * * * * MatchCase:=True, _ * * * * SearchFormat:=False) _ * * * * .Offset(0, 1).Value If strReplacement < "" Then _ * * cel.Replace _ * * * * What:=strNm, _ * * * * replacement:=strReplacement, _ * * * * LookAt:=xlWhole, _ * * * * SearchOrder:=xlByRows, _ * * * * MatchCase:=True, _ * * * * SearchFormat:=False, _ * * * * ReplaceFormat:=False End If Next cel On Error GoTo 0 End Sub Cliff Edwards Edward, The code replaces but has two bugs: - It is replacing every used cell in sheet - The code replacement is case sensitive Pls ammend & revert. Thx |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
A macro solution - replace text given the values
Sinner - change the range variable to the range you want processed:
In the following line, change "Sheet1.UsedRange" to whatever range you want to process. 'change to the range where your data is Set rngData = Sheet1.UsedRange There are two instances of this line in the sub: MatchCase:=True Replace them with this line: MatchCase:=False Cliff Edwards |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Replace values with text | Excel Worksheet Functions | |||
Automatically Accept Replace Values in Text-to-Columns macro | Excel Programming | |||
Find & Replace help solution? | Excel Programming | |||
Macro to replace error values | Excel Programming | |||
Append Text to Cell Values Using Replace | Excel Discussion (Misc queries) |