Detect Merged Cells in Columns and use a Formula if none-CodePudding

I'm currently working on merged cells, count and if statement.

So the issue is that for row 14, the output should be 0 or empty if there is a merged cell in the column, but if there isn't, it should apply the formula shown in the image.

If MERGE
Cell in Row 14 = Empty
Else
Cell in Row 14 = Formula

I'm thinking of this flow but I don't know how to start executing it.

CodePudding user response：

Cells(r, c).SelectandDebug.Print ActiveSheet.Cells(r, c).MergeArea.Address` is for you to debug (F8), for your formula, record macro fast and it works if necessary to understand and adapt.

To find out if the cell is merged:

If Len(ActiveSheet.Cells(r, c).MergeArea.Address) > 4 Then

however, note AA1... = Len($AA$10) = 6, and not is merge, ok.

Of course there are better ways to read spreadsheets than just this one. stay tuned for details.

whatever

Sub Detect_Merge()
Dim lr1 As Long, lc1 As Integer, maxR As Long, maxC As Integer
Dim MergeColunm As Boolean
ActiveSheet.PageSetup.Orientation = xlLandscape
    DisplayAlerts = True
    With ActiveSheet.UsedRange
        lr1 = .Rows.Count
        lc1 = .Columns.Count
    End With
    maxR = lr1
    maxC = lc1
    If maxR < lr1 Then maxR = lr1
    If maxC < lc1 Then maxC = lc1
    
    For c = 1 To maxC
    MergeColunm = False
        For r = 1 To maxR
        'Debug.Print ActiveCell.MergeArea.Columns.Count 'this not help
            If Len(ActiveSheet.Cells(r, c).MergeArea.Address) > 5 Then
                MergeColunm = True
                ActiveSheet.Cells(r, c).Select
                Debug.Print "Merge True"
                Debug.Print ActiveSheet.Cells(r, c).MergeArea.Address
                
            Else
                ActiveSheet.Cells(r, c).Select
                Debug.Print "Merge False"
                Debug.Print ActiveSheet.Cells(r, c).MergeArea.Address
            End If
            If (MergeColunm = False) And (r = 14) And (c >= 2) Then
                ActiveSheet.Cells(r, c).FormulaR1C1 = "=IF(R[-13]C="""","""",COUNTIF(R[-11]C:R[-1]C,"""")-COUNTBLANK(R3C1:R13C1))"
            ElseIf (MergeColunm = True) And (r = 14) And (c >= 2) Then
                ActiveSheet.Cells(r, c).FormulaR1C1 = "I don't do formulas - I write vba"
            End If
        Next r
    Next c
    MsgBox "Terminado - Finished"
End Sub