Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Merge cells in macro code (Excel xp)

    This is part of my macro. If there is a hyphen in rngCellT, i need to merge it with the cell next to it (to the right). How do i choose the activecell and the cell to the right and merge them in this loop? Thank you.

    For Each rngCellT In Range(rngCell.Offset(0, 1), rngCell.Offset(0, _
    rngCell.Offset(0, 256 - ActiveCell.Column).End(xlToLeft).Column - 1))
    If InStr((rngCellT.Value), "-") > 0 Then
    With rngCellT
    .NumberFormat = "@"
    .HorizontalAlignment = xlCenter
    .Value = "(" & .Value & ")"
    End With

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Merge cells in macro code (Excel xp)

    The ActiveCell and the cell to the right, or rngCellT and the cell to its right? Would this do what you want?

    <pre> Range(rngCellT, rngCellT.Offset(0, 1)).MergeCells = True
    </pre>

    Legare Coleman

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Merge cells in macro code (Excel xp)

    yes, this works perfectly. Thank you

    Then i tried...

    Range(rngCellT.offset(1,0), rngCellT.offset(1,1)).MergeCells=true to merge the cells 1 down and 1 to the right. For some reason it merges the 3 cells, 1 down and 1 AND 2 to the right. I only want 1 down and 1 to the right and i don't know how to stop getting 2. What am i doing wrong? thanks

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Merge cells in macro code (Excel xp)

    I copied your code from your message and pasted it into a VBA Sub and it seems to work perfectly. The only way I can get it to do what you say is if the cell one down and one to the right is already merged with the one that is one down and two to the right before I run the code.
    Legare Coleman

  5. #5
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Merge cells in macro code (Excel xp)

    well then something is wrong higher up the module. Here it is and it's merges 3 cells instead of 2.

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Merge cells in macro code (Excel xp)

    This is happening because the offset properties in the that statement are based on cells in rngCellT row which were merged in the previous statement, and that offset is from the end of the merged cells. To fix the problem, change the order of the three statements so that the cells in the rngCellT row are merged last, like this:

    <pre> Range(rngCellT.Offset(-1, 0), rngCellT.Offset(-1, 1)).MergeCells = True
    Range(rngCellT.Offset(1, 0), rngCellT.Offset(1, 1)).MergeCells = True
    Range(rngCellT, rngCellT.Offset(0, 1)).MergeCells = True
    </pre>

    Legare Coleman

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Merge cells in macro code (Excel xp)

    The problem is that you are offsetting from an already merged cell, merged in the prior line. So I switched the lines. I incorporated a few other changes, change your last "For Each rngCellT in
    -John ... I float in liquid gardens
    UTC -7ąDS

  8. #8
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Merge cells in macro code (Excel xp)

    Ok, thank you very much. I see the error of my ways. <img src=/S/bow.gif border=0 alt=bow width=15 height=15>

Posting Permissions

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