none
[Excel 2k3 + VBA] Runtime error '1004' Cannot change part of a merged cell RRS feed

  • Question

  • Hi,

    each time I try to apply template format with data from access I receive this message: Runtime error '1004' Cannot change part of a merged cell

    and it points to this line:
    ActiveSheet.Range(Cells(x, y), Cells(x + Int(c / 50), y + 6)).Clear
    

    and this is the complete section of the code:
    '*********************************************************************
    '* INPUT top left cell of the merge
    '* INPUT "[ROWS]#[COLUMNS]" offset to be merged (void = 0)
    '*********************************************************************
    Public Sub str_merge(row, col, instructions)
    Dim x, y As Integer
    Dim q As Variant
    q = Split(instructions, "#")
    y = q(UBound(q))
    If Not q(0) = "" Then x = q(0)
    ActiveSheet.Range(Cells(row, col), Cells(row + x, col + y)).MergeCells = True
    End Sub
    Public Function memo_merge(x, y, c)
    ActiveSheet.Range(Cells(x, y), Cells(x + Int(c / 50), y + 6)).Clear
    ActiveSheet.Range(Cells(x, y), Cells(x + Int(c / 50), y + 6)).Interior.color = RGB(255, 255, 255)
    ActiveSheet.Range(Cells(x, y), Cells(x + Int(c / 50), y + 6)).Borders.LineStyle = True
    ActiveSheet.Range(Cells(x, y), Cells(x + Int(c / 50), y + 6)).MergeCells = True
    memo_merge = x + Int(c / 50)
    End Function
    

    How to fix it, please?
    Best Regards
    Thursday, November 17, 2011 4:40 PM

Answers

  • fixed by changing:

    ActiveSheet.Range(Cells(x, y), Cells(x + Int(c / 50), y + 6)).Clear
    

     

    to

    ActiveSheet.Range(Cells(x, y), Cells(x + Int(c / 50), y + 6)).Value=""
    

     

    • Edited by Admin-Dev Thursday, November 17, 2011 7:34 PM refine
    • Marked as answer by Admin-Dev Thursday, November 17, 2011 7:34 PM
    Thursday, November 17, 2011 7:31 PM

All replies


  • Try running your Sub after executing the function.
    '---
    Jim Cone
    Portland, Oregon USA
    http://www.mediafire.com/PrimitiveSoftware
    (free and commercial excel programs)

    Thursday, November 17, 2011 6:04 PM
  • fixed by changing:

    ActiveSheet.Range(Cells(x, y), Cells(x + Int(c / 50), y + 6)).Clear
    

     

    to

    ActiveSheet.Range(Cells(x, y), Cells(x + Int(c / 50), y + 6)).Value=""
    

     

    • Edited by Admin-Dev Thursday, November 17, 2011 7:34 PM refine
    • Marked as answer by Admin-Dev Thursday, November 17, 2011 7:34 PM
    Thursday, November 17, 2011 7:31 PM
  • Hi Admin-Dev,

    You have answered you own question and if it works without error then go for it but here are a few notes that might interest you in case you do have a situation that errors.

    Your method will only work if the first cell of the merged range is included in the range to set to zero length string. eg if merged cells are B1:E1 and the range to set to zero length string is C1:E1 then it does not work.

    'For test the Merged cells are "B1:E1"
    'Values in merged cells plus values in A1 and F1 outside merged cells.
    ActiveSheet.Range("C1:F1").Value = ""     'Does not work on the merged cells

    ActiveSheet.Range("A1:F1").Value = ""  'Works because 1st cell of merged cells included

    If you use Clear on a merged range in a way that it fully includes the merged range then the merge formatting also gets cleared. Need to use ClearContents.

    Following is a generic way of clearing contents of cells when it is uncertain which cells are merged.

    Note: It is ClearContents so that merge is not cleared.

    'For test the Merged cells are "B1:E1"
    'Values in merged cells plus value in F1 outside merged cells.
    For Each cel In ActiveSheet.Range("C1:F1")
      If cel.MergeCells Then
        Range(cel.MergeArea.Address).ClearContents
      Else
        cel.ClearContents   'Single cell in range
      End If
    Next cel


    Regards, OssieMac
    • Edited by OssieMac Thursday, November 17, 2011 11:48 PM
    Thursday, November 17, 2011 11:46 PM