Find text string in one column and replace
Solved/Closed
Related:
- Find text string in one column and replace
- Display two columns in data validation list but return only one - Guide
- Vba select case string contains - Guide
- How to replace a word in word - Guide
- Excel count occurrences of string in column - Guide
- How to delete column in word - Guide
5 responses
RWomanizer
Posts
365
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
120
May 24, 2011 at 04:03 AM
May 24, 2011 at 04:03 AM
Could you please upload a sample EXCEL file WITH sample data, macro, formula , conditional formatting etc on some shared site like https://authentification.site , http://docs.google.com, http://wikisend.com/ , http://www.editgrid.com etc
AND post back here the link to allow better understanding of how it is now and how you foresee. Based on the sample book, could you re-explain your problem too.
Note: your data need not be to be real data but a good representative of how data looks like
AND post back here the link to allow better understanding of how it is now and how you foresee. Based on the sample book, could you re-explain your problem too.
Note: your data need not be to be real data but a good representative of how data looks like
Dear RWomanizer,
thans for your reply.As per sugesstion, uploaded sample,
link address.http://www.speedyshare.com/files/28649653/sample.xls
my code
[code]
Sub Replace_Text()
Dim lastrow As Long, rng As Range
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Application.ScreenUpdating = False
With Sheet1
.AutoFilterMode = False
Set rng = .Range("A1:H25" & lastrow)
rng.AutoFilter field:=3, Criteria1:="Replace*"
rng.Offset(1, 1).SpecialCells(12).Value = "Replacement"
.Cells(lastrow + 1, 2) = ""
.AutoFilterMode = False
End With
Application.ScreenUpdating = True
Set rng = Nothing
End Sub
/code
pl.look into this, if possible please
thans for your reply.As per sugesstion, uploaded sample,
link address.http://www.speedyshare.com/files/28649653/sample.xls
my code
[code]
Sub Replace_Text()
Dim lastrow As Long, rng As Range
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Application.ScreenUpdating = False
With Sheet1
.AutoFilterMode = False
Set rng = .Range("A1:H25" & lastrow)
rng.AutoFilter field:=3, Criteria1:="Replace*"
rng.Offset(1, 1).SpecialCells(12).Value = "Replacement"
.Cells(lastrow + 1, 2) = ""
.AutoFilterMode = False
End With
Application.ScreenUpdating = True
Set rng = Nothing
End Sub
/code
pl.look into this, if possible please
RWomanizer
Posts
365
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
120
May 26, 2011 at 12:54 AM
May 26, 2011 at 12:54 AM
Please use this code it will helps you,
Sub Replace_Text() Dim lastrow As Long Dim I As Integer lastrow = Cells(Rows.Count, "A").End(xlUp).Row Application.ScreenUpdating = False With Sheet1 For I = 2 To lastrow If Left(Cells(i, 3), 8) = "DOMESTIC" Then Cells(i, 7).Value = "Dom" Else Cells(i, 7).Value = "Replaced" End If Next i .AutoFilterMode = False End With Application.ScreenUpdating = True End Sub
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
May 26, 2011 at 07:22 AM
May 26, 2011 at 07:22 AM
You can try some thing like this.
Sub doReplacement() Dim lMaxRows As Long Dim rngLastCell As Range Dim lTestRow As Long With Sheet1 Application.ScreenUpdating = False .AutoFilterMode = False Set rngLastCell = .Cells.Find("*", .Cells(1, 1), , , xlByRows, xlPrevious) If rngLastCell Is Nothing Then GoTo doReplacement_Exit lMaxRows = rngLastCell.Row If lMaxRows = 1 Then GoTo doReplacement_Exit .Cells.AutoFilter field:=3, Criteria1:="Replace*" Set rngLastCell = .Cells.Find("*", .Cells(1, 1), , , xlByRows, xlPrevious) If rngLastCell Is Nothing Then GoTo doReplacement_Exit lTestRow = rngLastCell.Row If lTestRow = 1 Then GoTo doReplacement_Exit With .Range(.Cells(2, "G"), .Cells(lTestRow, "G")) .Value = "Replaced" End With End With doReplacement_Exit: Set rngLastCell = Nothing Sheet1.AutoFilterMode = False Application.ScreenUpdating = True End Sub
Didn't find the answer you are looking for?
Ask a question
Dear,
it seems not working.. not able to analyze with my little knowledge with XL.
Thanks for reply.
it seems not working.. not able to analyze with my little knowledge with XL.
Thanks for reply.
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
May 26, 2011 at 08:28 AM
May 26, 2011 at 08:28 AM
Strange. Worked for me. Could you post your workbook with the macro at https://accounts.google.com/ServiceLogin?passive=1209600&continue=https://docs.google.com/&followup=https://docs.google.com/&emr=1 and post the link here
Also what version of excel you are using
I have added comments for you, May be that will help you in understanding what the code was doing
Also what version of excel you are using
I have added comments for you, May be that will help you in understanding what the code was doing
Sub doReplacement() Dim lMaxRows As Long Dim rngLastCell As Range Dim lTestRow As Long Dim sTgtSheet As String 'name of the sheet on which the macro should act sTgtSheet = "Sheet1" With Sheets(sTgtSheet) 'freezing the sceen so processing is not shown Application.ScreenUpdating = False 'remove any exisitng filter .AutoFilterMode = False 'find the cell that contains some thing on the last row in use Set rngLastCell = .Cells.Find("*", .Cells(1, 1), , , xlByRows, xlPrevious) ' if nothing is found, then sheet is blank and get out If rngLastCell Is Nothing Then GoTo doReplacement_Exit ' find out row on which the cell was found lMaxRows = rngLastCell.Row 'if the row was 1, then get out If lMaxRows = 1 Then GoTo doReplacement_Exit 'apply th efilter on column 3 and criteria being any thing that starts with Replace .Cells.AutoFilter field:=3, Criteria1:="Replace*" 'find out how last row (only visible row would be scanned) Set rngLastCell = .Cells.Find("*", .Cells(1, 1), , , xlByRows, xlPrevious) 'find if any row is visible , if not exit If rngLastCell Is Nothing Then GoTo doReplacement_Exit 'find the last visible row lTestRow = rngLastCell.Row 'if last visible row =1 then exit If lTestRow = 1 Then GoTo doReplacement_Exit 'in all visible cells of column G, after row 1, put "Replaced" With .Range(.Cells(2, "G"), .Cells(lTestRow, "G")) .Value = "Replaced" End With doReplacement_Exit: 'free up memory Set rngLastCell = Nothing 'remove any filter .AutoFilterMode = False 'let screen refresh Application.ScreenUpdating = True End With End Sub
Jun 9, 2011 at 07:28 AM
In continuation to your help,
If numbers in cloumn 3 instead of Text, is it possible to set criteria. Say a number starts with 3 in a row , then respective Row in column G to be changed as 'REPLACE".
Kindly provide code.. Many thanks in advance.
Jun 9, 2011 at 04:25 PM
Jun 9, 2011 at 08:57 PM
Column c Col G
1000025 X
3251200 X
3000025 Y
2048899 Y
1200000 X
2000555 Y
2586999 Y
4221155 Z
3025141 Z
If Number in column C starts with 1 then G Always ' X', and if starts with 2 then G Always ' y'.
only if this number starts with 3 then in G may be X or Y or Z.
Code works fine in Text but not in Numbers, Currently I am doing this step manually then applying other macros. if possible this step also run with code.
Pl. help me.
Jun 10, 2011 at 09:37 AM
Jun 15, 2011 at 07:24 AM
Some times Length of the number varies , also starts with 5. So, some need some ways to sort numbers start with particular digit.