Offset with merged cells

Question

We receive the insurance periods from our insured persons from various companies around the world in Excel format.

I read the data automatically, but these Excel files contain merged cells. These are merged differently.

What is always the same is that the next cells to the right of the starting cell contain the desired information.
I need to always determine the data to the right of the starting cell, but considered that they are merged cells. Can .offset do this?

Example:
A5:C5 merged, D5 not merged, E5:H5 merged, I5:P5 merged
--> I need the data from D5, E5 and I5 (cells to the right of it)

For the next insured, the same data is formatted as follows:
A5:B5 merged, C5:F5 merged, G5:J5 merged, K5:O5 merged
--> I need the data from C5, G5 and K5 (cells to the right of it)

It is always the three cells to the right of it, but right in terms of merged cells.

This is what I tried:
From start c is AN87 and the NewAddress gives me AM87 even though AK87:AM87 is merged.

Dim c As Range
Dim firstAddress As String

With Workbooks(Left(myworksheet.Range("E10").Value, Len(myworksheet.Range("E10").Value) - 4) & ".xlsx").Worksheets("Sheet1").Range("A1:AY1000")
    Set c = .Find(myworksheet.Range("E11").Value, LookIn:=xlValues, Lookat:=xlWhole)
    If Not c Is Nothing Then
        ElzPeter = c.Address
        MsgBox ElzPeter
    End If
End With

Dim MA As Range, NewAddress As String
Set MA = c.MergeArea
NewAddress = MA.Offset(, -1).Resize(MA.Rows.Count).Address
MsgBox NewAddress

Answer 1

If you have a merged cell, .Offset(0, 1) will always give you the first cell to the right of the merged area. So if cells "A5:C5" are merged, Range("A5").Offset(0, 1) will give you "D5".

Assuming that you start at "A5", the following should do the trick for you:

With ActiveSheet       ' Specify the sheet you want to work with
    Set r = .Range("A5")
    For i = 1 To 3       
        Set r = r.Offset(0, 1)
        Debug.Print r.Address, r.MergeArea.Address, r.Value
    Next
End With

Update
If you want to go from right to left: Offset(0, -1) will give you the last cell of the merged area. From there, you can get the value of the merged cells with MergeArea.Cells(1, 1)

Set r = .Range("AN87")
For i = 1 To 3       
    Set r = r.Offset(0, -1)
    Debug.Print r.Address, r.MergeArea.Address, r.MergeArea.Cells(1, 1).Value
Next