Find text string in one column and replace

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.

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.

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.

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.

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

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

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