+ Reply to Thread
Results 1 to 2 of 2

VBA Excel 2007 : Autofit Merge Cells Error "Run-time error '13': Type Mismatch"

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    02-16-2012
    Location
    Philippines
    MS-Off Ver
    Excel 2007
    Posts
    108

    VBA Excel 2007 : Autofit Merge Cells Error "Run-time error '13': Type Mismatch"

    Hello -

    I am desperately in need of your help because I can't seem to understand why the below script keeps giving me this error "Run-time error '13': Type Mismatch". The part with a red font is where the error is coming.

    Can you please help me in figuring out what I did wrong?

    Private Sub AutosizeMergedCells()
    Dim NewRwHt As Single
    Dim cWdth As Single, MrgeWdth As Single
    Dim c As Range, cc As Range
    Dim ma As Range
    Dim r2 As Range
    Dim cell As Range
    Dim ws As Worksheet
    
    Application.ScreenUpdating = False
    
    If ActiveSheet.Name <> "amform" Then
        MsgBox "You can only run this process when you are on the EVALUATION FORM. This process has been canceled."
        Exit Sub
    End If
    
    Set ln1 = Worksheets("amform")
    Set ln1a1 = ln1.Range("A20")
    Set ln1a2 = ln1a1
    
    Application.ScreenUpdating = False
    Oblean = True
    ln1.Unprotect Password:="lplains"
    
    Do While Left(ln1a1.Offset(0, 1), 2) <> "B."
            Set ln1a1 = ln1a1.Offset(1)
    Loop
        Set ln1a3 = ln1.Range(ln1a2.Offset(, 1), ln1a1.Offset(-2, 15))
    
    Set ln1a2 = ln1a1
    Do While Left(ln1a1.Offset(0, 1), 2) <> "C."
            Set ln1a1 = ln1a1.Offset(1)
    Loop
        Set ln1a4 = ln1.Range(ln1a2.Offset(2, 1), ln1a1.Offset(-2, 15))
    
    Set ln1a2 = ln1a1
    Do While Left(ln1a1.Offset(0, 1), 2) <> "D."
            Set ln1a1 = ln1a1.Offset(1)
    Loop
        Set ln1a5 = ln1.Range(ln1a2.Offset(2, 1), ln1a1.Offset(-2, 15))
    
     Set ln1a2 = ln1a1
    Do While Left(ln1a1.Offset(0, 1), 2) <> "E."
            Set ln1a1 = ln1a1.Offset(1)
    Loop
    Set ln1a2 = ln1a1
    Do While Left(ln1a1.Offset(0, 1), 2) <> "F."
            Set ln1a1 = ln1a1.Offset(1)
    Loop
        Set ln1a6 = ln1.Range(ln1a2.Offset(2, 1), ln1a1.Offset(-2, 15))
    
    For Each cell In Union(ln1a3, ln1a4, ln1a5, ln1a6)
    'On Error Resume Next
     If cell.MergeCells Then
       Set r2 = cell.MergeArea
       If cell.Address = r2(1).Address Then
        If Len(Trim(cell.Value)) <> 0 Then
        With cell
         If .MergeCells And .Text Then
          Set c = cell.Cells(1, 1)
          cWdth = c.ColumnWidth
          Set ma = c.MergeArea
          For Each cc In ma.Cells
            MrgeWdth = MrgeWdth + cc.ColumnWidth
          Next
          Application.ScreenUpdating = False
          ma.MergeCells = False
          c.ColumnWidth = MrgeWdth
          c.EntireRow.AutoFit
          NewRwHt = c.RowHeight
          c.ColumnWidth = cWdth
          ma.MergeCells = True
          ma.RowHeight = NewRwHt
          cWdth = 0: MrgeWdth = 0
         End If
        End With
        End If
       End If
     End If
    Next cell
    ln1.Protect Password:="lplains"
    Application.ScreenUpdating = True
    End Sub
    Thank you very much!!!!
    Last edited by Hudas; 07-19-2012 at 04:32 PM.

  2. #2
    Forum Expert Mordred's Avatar
    Join Date
    07-06-2010
    Location
    Winnipeg, Canada
    MS-Off Ver
    2007, 2010
    Posts
    2,787

    Re: VBA Excel 2007 : Autofit Merge Cells Error "Run-time error '13': Type Mismatch"

    I'm not sure if I am on the right track here but .Text means nothing in that context. The .Text should equal = or <> something.

    EDIT: That's definitely the problem.
    Last edited by Mordred; 07-18-2012 at 04:14 PM.
    If you're happy with someone's help, click that little star at the bottom left of their post to give them Reps.

    ---Keep on Coding in the Free World---

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1