Find text string in one column and replace [Solved/Closed]

- - Latest reply:  PMARI - Jun 15, 2011 at 07:24 AM
Hello,

I need a macro to compare two columns, only if particular texts in column C, text in adjacent row of column D should be changed. Text in other columns remains same.

No.of rows not fixed.

Thanks in Advance.
See more 

5 replies

Best answer
2
Thank you
Dear rizvisa1,

Many thanks for your code. it is working fine.

Thanks once again.

P.M.

Say "Thank you" 2

A few words of thanks would be greatly appreciated. Add comment

CCM has helped 2734 users this month

Dear rizvisa1,

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.
rizvisa1
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
-
If you look at the code. It is nothing but a filter. So if you can do it manually the filter that you are talking about, you can do in the code too. If it is a truly a number that you would need some sort of work around.
Dear Boss,

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.
rizvisa1
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
-
Does the length of number (number of digits in the number) remains same (as in your sample). If that is the case then you can use >=3000000
Hi Boss

Some times Length of the number varies , also starts with 5. So, some need some ways to sort numbers start with particular digit.
Posts
368
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
0
Thank you
Could you please upload a sample EXCEL file WITH sample data, macro, formula , conditional formatting etc on some shared site like http://www.speedyshare.com/ , 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
0
Thank you
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
Posts
368
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
0
Thank you
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

Daer Mr.RW,

Many thanks for your time and reply. it is working well in given sample. But I made a mistake in sending sample file. can this code be slightly modified for the sample sheet.

http://www.speedyshare.com/files/28652719/SAMPLE2.xls

Kindly consider.

Best Regards, PM.
rizvisa1
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
-
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
0
Thank you
Dear,

it seems not working.. not able to analyze with my little knowledge with XL.

Thanks for reply.
rizvisa1
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
-
Strange. Worked for me. Could you post your workbook with the macro at https://docs.google.com/ 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

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