Range.Find Result Changing Irrationally After Inserting Columns and Naming Cells

[matt767]

Dim rngY As Range
Set rngY = ActiveSheet.Range("A1:MA1").Find("Main Image", lookat:=xlPart)
Dim rngcol2 As Integer
rngcol2 = rngY.Column
Columns(rngcol2).Insert
rngY.Offset(0, -1).Value = "Images"
rngY.Offset(1, -1).Value = "images"
Dim lr As Long
lr = Application.WorksheetFunction.CountA(Columns(2))
rngY.Offset(1, 0).FormulaR1C1 = _
        "=TEXTJOIN("","",TRUE,RC[1],RC[2],RC[3],RC[4],RC[5],RC[6],RC[7],RC[8],RC[9],RC[10])"
rngY.Offset(1, 0).Select
Selection.AutoFill Destination:=Range(rngY.Offset(1, 0), Cells(lr, rngcol2))
Columns(rngcol2).Copy
Columns(rngcol2).PasteSpecial Paste:=xlPasteValues
Columns(rngcol2).Copy
Columns(rngcol2).Select
Selection.Insert Shift:=xlToRight
rngY.Offset(1, -1).Value = "variant.images"
Range(Cells(1, rngcol2 + 2), Cells(1, rngcol2 + 11)).EntireColumn.Delete

My workbook has two header rows on top followed by the data rows. This code works as intended but the rngY range changes oddly - It should be cell R1 at first ('Main Image' cell is there before inserting columns) but then it becomes S1 after inserting the first column, then it becomes R2 (without inserting any more columns) after naming the two new header cells in the newly inserted column and defining lr as the last row, then it changes back to S1 after copying column R and inserting it to left (while the 'Main Image' cell is now T1). Whereas rngcol2 is the same number throughout. I just want to understand why rngY behaves so oddly without any apparent logic, which forced me to adapt my offsets to its strange behavior. Thank you.

 

[matt767]

rngY actually is T1 in the last instance. So the only strange thing occurring is when it becomes R2.

 

[matt767]

I ran it again with the following changed code and it worked the same as before:

Dim rngY As Range
Set rngY = ActiveSheet.Range("A1:MA1").Find("Main Image", lookat:=xlPart)
Dim rngcol2 As Integer
rngcol2 = rngY.Column
Columns(rngcol2).Insert
rngY.Offset(0, -1).Value = "Images"
rngY.Offset(1, -1).Value = "images"
Dim lr As Long
lr = Application.WorksheetFunction.CountA(Columns(2))
rngY.Offset(2, -1).FormulaR1C1 = _
        "=TEXTJOIN("","",TRUE,RC[1],RC[2],RC[3],RC[4],RC[5],RC[6],RC[7],RC[8],RC[9],RC[10])"
rngY.Offset(2, -1).Select
Selection.AutoFill Destination:=Range(rngY.Offset(2, -1), Cells(lr, rngcol2))
Columns(rngcol2).Copy
Columns(rngcol2).PasteSpecial Paste:=xlPasteValues
Columns(rngcol2).Copy
Columns(rngcol2).Select
Selection.Insert Shift:=xlToRight
rngY.Offset(1, -2).Value = "variant.images"
Range(Cells(1, rngcol2 + 2), Cells(1, rngcol2 + 11)).EntireColumn.Delete

For some reason the old code worked fine when it shouldn't have. rngY.Offset(1, 0).FormulaR1C1 should have incorrectly began the textjoin a cell below the Main Image cell in (S2) when it actually started it in rngY.Offset(2, -1) or R3, an offset the above code suddenly works fine with.