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

PMARI - May 22, 2011 at 02:54 AM - 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 

13 replies

Best answer
2
Thank you
Dear rizvisa1,

Many thanks for your code. it is working fine.

Thanks once again.

P.M.

Thank you, PMARI 2

Something to say? Add comment

CCM has helped 1302 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 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Jun 9, 2011 at 04:25 PM
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 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Jun 10, 2011 at 09:37 AM
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.
RWomanizer 368 Posts Monday February 7, 2011Registration dateContributorStatus September 30, 2013 Last seen - May 24, 2011 at 04:03 AM
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
RWomanizer 368 Posts Monday February 7, 2011Registration dateContributorStatus September 30, 2013 Last seen - May 26, 2011 at 12:54 AM
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 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - 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
0
Thank you
Dear,

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

Thanks for reply.
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - May 26, 2011 at 08:28 AM
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