Loop with an unintentional offset

[cacahuatitaCH]

So, I found this looping code and adapted it a bit, pretty superficial changes.

It works well for the first instance, finds the text I asked it to, changes the next row... but after that, it keeps changing the text in the cell it finds the actual text its supposed to be looking for as a reference.

Could you help me find the mistake?

Sub Default_test()
   Dim Ary As Variant
   Dim i As Long
  
   Ary = Array("Actual Results", "BLANK")
   With ActiveSheet.Range("A5:F550")
      For i = 0 To UBound(Ary) Step 2
         .Replace Ary(i), "=" & Ary(i), xlWhole, , False, , False, False
         .SpecialCells(xlFormulas, xlErrors).Offset(1, 0).Value = Ary(i + 1)
         .Replace "=" & Ary(i), Ary(i), xlWhole, , False, , False, False
      Next i
   End With
  
 MsgBox "DONE!"
End Sub

 

[Fluff]

What do you mean it works the first time, but not after that? You are only looking to change one value

 

[cacahuatitaCH]

What do you mean it works the first time, but not after that? You are only looking to change one value

The fist instance, it works as it should, but once it starts looking for the second, third and so on, it changes the cell value where it finds the text "Actual Result"

 

[Fluff]

Can you ever have two adjacent cells with Actual Result?

Also the code doesn't look a 2nd 3rd time, it does it all at once.

 

[cacahuatitaCH]

Can you ever have two adjacent cells with Actual Result?

Also the code doesn't look a 2nd 3rd time, it does it all at once.

No, there´s no adjacent cell repetition.

The thing is, that it works properly on the first instance only, after that it substitutes the "Actual Result" text

 

[Fluff]

Works quite happily for me

+Fluff v2.xlsm
	A	B	C	D	E
5	Cumbria	Eden	Ullswater	Penrith and The Border	Matterdale
6	Shropshire	Shropshire	Cleobury Mortimer	Ludlow	Farlow
7	Devon	Torridge	Waldon	Torridge and West Devon	Milton Damerel
8	Actual Results	Stockport	Actual Results	Hazel Grove
9	BLANK	County Durham	BLANK	North Durham	Stanley
10	Staffordshire	Stoke-on-Trent	Birches Head and Central Forest Park	Stoke-on-Trent Central
11	Buckinghamshire	Actual Results	Hambleden Valley	Wycombe	Hambleden
12	Lancashire	BLANK	Boulsworth	Pendle	Colne
13	North Yorkshire	Hambleton	OsFatherley & Swainby	Actual Results	OsFatherley
14	West Midlands	Birmingham	Longbridge	BLANK
15	West Yorkshire	Kirklees	Kirkburton	Dewsbury	Actual Results
16	Cornwall	Cornwall	St Teath and St Breward	North Cornwall	BLANK
17	West Yorkshire	Kirklees	Colne Valley	Colne Valley
18	Actual Results	Pendle	Actual Results	Pendle	Actual Results
19	BLANK	Bradford	BLANK	Bradford South	BLANK
20	Cumbria	Eden	Crosby Ravensworth	Penrith and The Border	Crosby Ravensworth
21	Derbyshire	Amber Valley	Alport	Derbyshire Dales	Dethick, Lea and Holloway
Data

Do you have any merged cells?

 

[cacahuatitaCH]

both the cells containing "Actual Results" and the ones to write "blank" into are merged, only horizontally, they do not over more than one row

 

[Fluff]

In that case it should not be a problem with this code.
Do any of the cells have formulae?

 

[cacahuatitaCH]

In that case it should not be a problem with this code.
Do any of the cells have formulae?

Some of the cells to be changed to "blank" do have formulas... with no reference to the phrase"actual results".

 

[Fluff]

In that case I don't know, do you have any other values in the array to replace, or just Actual results?