CODEWITHSUNDEEP
vbaexcel
toupenator
toupenator

Reputation: 13

Having trouble merging cells in VBA

So I'm writing some VBA code that goes through my document and looks for where a formula returns an error and it merges and centers it with the cell that's underneath it.

Private Sub CommandButton22_Click()
     Dim strTemp As String
     Dim ev As Variant
     Dim rng As Range, cell As Range
     Set rng = Range("H87:H89")


For Each cell In rng
    If (cell.HasFormula) Then
        cell.Select

        strTemp = ActiveCell.Formula
        ev = Evaluate(strTemp)

        If (IsError(ev)) Then
            ActiveCell.Clear
            ActiveCell.Merge ([0,1])

        End If
    End If
Next cell
End Sub

This is what I have so far, it clears the cell properly but won't merge.

Upvotes: 1

Views: 746

Answers (2)

Tim Williams
Tim Williams

Reputation: 166885

Something like this. Note you rarely need select/activate and should try to avoid it as much as possible.

Private Sub CommandButton22_Click()

    Dim cell As Range

    For Each cell In Range("H87:H89").Cells
        If cell.HasFormula Then
            If IsError(cell.Value) Then
                cell.Clear
                cell.Resize(2, 1).Merge
            End If
        End If
    Next cell

End Sub

Upvotes: 0

user8753746
user8753746

Reputation:

Try using:

Range(Cells(ActiveCell.Row, ActiveCell.Column), Cells(ActiveCell.Row + 1, ActiveCell.Column)).Merge

Hope it helps.

Upvotes: 1

Related Questions