Loop the code untill end

[mayurpatil]

Hi,

I have a code which searches for 2 values in a column and replaces value in rows in between those values. But i have many cobinations like this and the code works for only first , so i just want to loop untill last cell in excel.

My code is :

Sub Test() 
Dim F1 As Range, F2 As Range
With ActiveSheet
Set F1 = .Columns("H").Find(What:="FIRST", LookIn:=xlValues, LookAt:=xlWhole)
Set F2 = .Columns("H").Find(What:="SECOND", LookIn:=xlValues, LookAt:=xlWhole)
If Not F1 Is Nothing And Not F2 Is Nothing Then ActiveSheet.Rows(F1.Row & ":" & F2.Row).Select

Selection.Replace What:="XG13", Replacement:="REPLACED", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="XG14", Replacement:="REPLACED", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End With
End Sub

Kindly help with this ,

Thanks & Regards ,

Mayur

[JOHN H. DAVIS]

Maybe:

Sub mayurpatil()
Dim x As Long
x = ActiveSheet.UsedRange.Rows.Count
For Each cell In Range("H2:H" & ActiveSheet.UsedRange.Rows.Count)
    If cell.Value = "FIRST" Then cell.Select
    Do Until ActiveCell.Value = "SECOND" Or ActiveCell.Value = "" Or ActiveCell.Row = x
        ActiveCell.Replace "XG13", "REPLACED", xlPart
        ActiveCell.Replace "XG14", "REPLACED", xlPart
        ActiveCell.Offset(1).Select
    Loop
Next cell
End Sub

Untested, try on a sample first.

[mayurpatil]

Hi,

Sorry not working John . Please if anyone can just loop the code . Bcoz its working justr not getting loop .

[JOHN H. DAVIS]

Can you attach a file with some sample data?

To attach a file, push the button with the paperclip (or scroll down to the Manage Attachments button), browse to the required file, and then push the Upload button.

[jeffreybrown]

@Mayur,

Administrative Note:

• We would love to continue to help you with your query, but first, before we can proceed…
• Please see Forum Rule #3 about the proper use of code tags and adjust accordingly...

[mayurpatil]

Hi,jeffreybrown

My apologies for the same , I will reframe the question.

I have a code which searches for 2 values in a column and replaces value in rows in between those values. But i have many cobinations like this and the code works for only first , so i just want to loop untill last cell in excel.

My code is :

Sub Test() 
Dim F1 As Range, F2 As Range
With ActiveSheet
Set F1 = .Columns("H").Find(What:="FIRST", LookIn:=xlValues, LookAt:=xlWhole)
Set F2 = .Columns("H").Find(What:="SECOND", LookIn:=xlValues, LookAt:=xlWhole)
If Not F1 Is Nothing And Not F2 Is Nothing Then ActiveSheet.Rows(F1.Row & ":" & F2.Row).Select

Selection.Replace What:="XG13", Replacement:="REPLACED", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="XG14", Replacement:="REPLACED", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End With
End Sub

Kindly help with this ,

Thanks & Regards ,

Mayur