2

I am configuring a long IF statement, where if the financial period is the same, it merges the cells together under the same period header, and if not, it creates a new header with the new period name. The formatting functions seem to work fine on their own. However, I keep running into a compile error, Else without If. This happens even though there is an If statement proceeding it. I have looked through the forums, and I cannot seem to find a solution. Here is my code:

Dim LC As Long
LC = Cells(1, Columns.Count).End(xlToLeft).Column

If Range(LC & "1").Value = Range(LC - 1 & "2").Value Then
    Dim prd As Long
    prd = Range(LC - 1 & "2").MergeArea.Address
    Range(prd).UnMerge
    prd = Union(Range(prd), Range(LC & "2"))
    Range(prd).Merge
    With Range(prd)
        With .Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
            .TintAndShade = 0
            .Weight = xlMedium
        End With
        With .Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlMedium
        End With
    With Range(LC & "3", LC & "24")
        .Borders(xlEdgeLeft).LineStyle = xlNone
        With .Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .ColorIndex = xlAutomatic
            .TintAndShade = 0
            .Weight = xlMedium
        End With
    End With
Else
    With Range(LC & "2")
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .Font.Bold = True
        .FormulaR1C1 = "=R[-1]C"
        .Value = .Value
        With .Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlMedium
        End With
        With .Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlMedium
        End With
    End With
End If
1
  • 2
    This With Range(prd) on the code you provided does not have an End With. Mar 4 at 19:52

1 Answer 1

4

I think you forgot the End With of With Range(prd).

Try if this works:

Dim LC As Long
    LC = Cells(1, Columns.Count).End(xlToLeft).Column
    
    If Range(LC & "1").Value = Range(LC - 1 & "2").Value Then
        Dim prd As Long
        prd = Range(LC - 1 & "2").MergeArea.Address
        Range(prd).UnMerge
        prd = Union(Range(prd), Range(LC & "2"))
        Range(prd).Merge
        With Range(prd)
            With .Borders(xlEdgeTop)
                .LineStyle = xlContinuous
                .ColorIndex = xlAutomatic
                .TintAndShade = 0
                .Weight = xlMedium
            End With
            With .Borders(xlEdgeRight)
                .LineStyle = xlContinuous
                .ColorIndex = 0
                .TintAndShade = 0
                .Weight = xlMedium
            End With
        End With
        With Range(LC & "3", LC & "24")
            .Borders(xlEdgeLeft).LineStyle = xlNone
            With .Borders(xlEdgeRight)
                .LineStyle = xlContinuous
                .ColorIndex = xlAutomatic
                .TintAndShade = 0
                .Weight = xlMedium
            End With
        End With
    Else
        With Range(LC & "2")
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlBottom
            .Font.Bold = True
            .FormulaR1C1 = "=R[-1]C"
            .Value = .Value
            With .Borders(xlEdgeTop)
                .LineStyle = xlContinuous
                .ColorIndex = 0
                .TintAndShade = 0
                .Weight = xlMedium
            End With
            With .Borders(xlEdgeRight)
                .LineStyle = xlContinuous
                .ColorIndex = 0
                .TintAndShade = 0
                .Weight = xlMedium
            End With
        End With
    End If
1
  • Ah... thank you! I knew it had to be something ridiculous I was missing. This fixed the issue. Thank you!
    – Matt
    Mar 7 at 16:44

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .