Macro to Find and Replace

[skmerrey]

Hi Everyone,

I'm using the below code to find and replace "100" with "Term 1" in Column F.

I now need it to find any number between 1-200 and replace with Term 1.

Sub FindReplace()

Columns("F").Replace What:="100", _
                            Replacement:="Term 1", _
                            LookAt:=xlPart, _
                            SearchOrder:=xlByRows, _
                            MatchCase:=False, _
                            SearchFormat:=False, _
                            ReplaceFormat:=False

End Sub

Any Help is appreciated.
Shaun

 

[shrivallabha]

You can use loop like below:

Sub FindReplace()
'Loop through numbers
For i = 200 to 1 step -1
Columns("F").Replace What:=i, _
                            Replacement:="Term 1", _
                            LookAt:=xlPart, _
                            SearchOrder:=xlByRows, _
                            MatchCase:=False, _
                            SearchFormat:=False, _
                            ReplaceFormat:=False
Next i
End Sub

 

[Marc L]

Hi !

Another way :​

Sub Demo()
    With ActiveSheet.UsedRange.Columns(6)
            VA = .Value
        For R& = 1 To UBound(VA)
            If VA(R, 1) >= 1 And VA(R, 1) <= 200 Then VA(R, 1) = "Term 1"
        Next
            .Value = VA
    End With
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[ANKUSHRS1]

my approach

Sub FindReplace()

Dim i As Long
Dim lastrow As Long

lastrow = Cells(Sheet1.Rows.Count, "f").End(xlUp).Row

  MsgBox lastrow
 
  For i = 1 To lastrow
 
  If Range("f" & i).Value > 0 And Range("f" & i).Value <= 200 Then
 
  Range("f" & i).Value = "Term 1"
 
  End If
  Next 
 
End Sub

 

[shrivallabha]

OP is using "xlPart" in his replace code which made me think that the cells might be having "200 and more" rather than just "200". I could be wrong also

 

[skmerrey]

Thanks, both work great