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

    Move letters to next column (Excel xp)

    I've been using this macro that searches a column of numbers and letters. If it finds a letter it moves it to the next column to the right and next to the number above it. The problem is that some of the cells have a "#" and some have a ">" sign in them so when the macro encounters them it doesn't move the letter below it to the right and one row up. I tried adding to the If statement but that didn't work or do i have to change the specialcells code. Thank you for the help.

    Sub MoveLetter()
    For Each cell In Selection.SpecialCells(xlConstants, xlNumbers)
    cell.Offset(1, 0).Range("A1").Select ' Down one cell...
    If Not (IsNumeric(ActiveCell.Text)) Or ActiveCell.Text = ">" Then ' if the cell is not a number
    sCellBelowContents = ActiveCell.Text ' copy the source.
    ActiveCell.Value = "" ' delete the source.
    cell.Offset(0, 1).Range("A1").Select ' One to the right...
    ActiveCell.Value = sCellBelowContents ' paste data into destination cell.
    End If
    Next cell
    End Sub

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Move letters to next column (Excel xp)

    Could you provide a small column of sample data please (and what should go where)?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

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

    Re: Move letters to next column (Excel xp)

    Thank you for looking at this.

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Move letters to next column (Excel xp)

    From the sheet you've provided it is impossible to me to grasp what should go where.

    Please provide a small subset of the data that needs to be reorganised, with an explanation what should go where.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

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

    Re: Move letters to next column (Excel xp)

    Jan Karel, he wants to reorganize the layout from that in the Before sheet to that in the After sheet. It's a matter of inserting a column and using offset to move the data in the rows where column A has no content, then deleting the unwanted rows.
    -John ... I float in liquid gardens
    UTC -7ąDS

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

    Re: Move letters to next column (Excel xp)

    yes, sorry about that. My problem is the # and the > signs, since the present macro is only recognizing numerals. I don't know how to include the # and > signs in the macro. thanks

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Move letters to next column (Excel xp)

    Does this do what you want (I didn' t format column widths or change alignment)?
    It turns before into after

    Steve
    <pre>option explicit
    Sub jha900Routine()
    Range("C33").EntireColumn.Insert
    Range(Range("B3"), Range("B65536").End(xlUp)).Copy (ActiveSheet.Range("C2"))
    Range(Range("e3"), Range("e65536").End(xlUp)).Copy (ActiveSheet.Range("F2"))

    Range(Range("a2"), Range("a65536").End(xlUp)).Offset(1, 0). _
    SpecialCells(xlCellTypeBlanks).Select
    Selection.EntireRow.Delete
    Range("a1").Select
    End Sub

    </pre>


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

    Re: Move letters to next column (Excel xp)

    Steve's version may be easier to work with, mine includes the fomatting stuff towards the end:

    Sub Macro1()
    Dim rngBlanks As Range, rngCell As Range
    On Error Resume Next
    Set rngBlanks = Columns("A:A").SpecialCells(xlCellTypeBlanks)
    If rngBlanks Is Nothing Then Exit Sub
    Columns("C:C").Insert Shift:=xlToRight
    Columns("C:C").Insert Shift:=xlToRight
    For Each rngCell In rngBlanks
    rngCell.Offset(-1, 2).Value = rngCell.Offset(0, 1).Value
    rngCell.Offset(-1, 5).Value = rngCell.Offset(0, 4).Value
    Next rngCell
    Set rngBlanks = Nothing
    For Each rngCell In Intersect(Columns("A:A"), ActiveSheet.UsedRange)
    If rngCell.Value = "" Then rngCell.EntireRow.Delete Shift:=xlUp
    Next rngCell
    Range("B:B,E:E").HorizontalAlignment = xlRight
    Range("C:C,F:F").ColumnWidth = 3
    Range("D,G:G").ColumnWidth = 1
    End Sub
    -John ... I float in liquid gardens
    UTC -7ąDS

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

    Re: Move letters to next column (Excel xp)

    oh my gosh. this was too cool. Could u explain this line...

    Range(Range("a2"), Range("a65536").End(xlUp)).Offset(1, 0). _
    SpecialCells(xlCellTypeBlanks).Select

    how did it copy the cell underneath to the cell to the right?

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Move letters to next column (Excel xp)

    range:
    starting at:
    Range("A2")
    Ending at:
    Range("a65536").End(xlUp)).Offset(1, 0)
    This starts at a65536 (last row of col A) goes end up to get to last cell in range. Offset goes 1 row down, 0 columns to right to be the last cell.

    Then it selects the blanks within the range

    Step thru the code, line by line and I think you will get a good feel for what it does.

    Steve

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

    Re: Move letters to next column (Excel xp)

    ok, thanks. Actually i didn't see that when you copied it copied it one cell up from B3 to B2. That was very original.

    How can i make the code more flexible for when i don't know if it will start on A3 or A10 and I don't know how many columns there will be? I have many files that need this macro and want to use it on all of them. Thanks much.

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Move letters to next column (Excel xp)

    It depends.
    If you start in row 10 what will be in row 1-9? If you run my code, many of thsoe rows at the top will just be deleted as blank rows, so it might not matter.

    For extra columns, just loop thru them (last to first might be easiest.)
    For each column insert 2 cols after it and then copy as before 1 column over
    If desired adjust the column width, align the cells
    then go over to the next earlier column and repeat
    just loop through until you are at the 1st column.

    Steve

  13. #13
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Move letters to next column (Excel xp)

    Thanks John (and Steve) for jumping in.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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