VBA Excel - Offset with merged cells-CodePudding

Yes, you maybe think, why the hell did he even use merged cells. Let me explain:

We receive the insurance periods from our insured persons from various companies around the world in Excel format. I have to read this data automatically, but I have the problem that these Excel files contain merged cells. These are always merged differently. However, what is always the same is that the next cells to the right of the starting cell contain the desired information. So I would need a code 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)

So it's always the 3 cells to the right of it, but right in terms of merged cells.

Can someone help me? Thanks!

Update: 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

CodePudding user response：

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