Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    transpose macro (2003)

    I have a transpose macro that I got from this site that I am trying to adjust to different parameters. In column G, I have a number. Every time that number changes, I want the verbage in column H to transpose to the side of that number.
    01234567 To be or
    not to be
    that is the question
    01235555 Yes withing
    12 months
    following the end of benefit period

    The macro is as follows:

    Public Sub TransposeRows3()
    Dim oLastCell As Range, oFirstCell As Range
    With Worksheets("Sheet1")
    Set oLastCell = .Range("G65536").End(xlUp)
    If oLastCell.Offset(0, -1).Value <> "" Then
    Set oFirstCell = oLastCell
    Else
    Set oFirstCell = oLastCell.Offset(0, -1).End(xlUp).Offset(0, 1)
    End If
    Do While Not oFirstCell Is Nothing
    Range(oFirstCell, oLastCell).Copy
    oFirstCell.Offset(0, 1).PasteSpecial Transpose:=True
    If oFirstCell.Row = 1 Then
    Set oFirstCell = Nothing
    Set oLastCell = Nothing
    Else
    Set oLastCell = oFirstCell.Offset(-1, 0)
    If oLastCell.Offset(0, -1).Value <> "" Then
    Set oFirstCell = oLastCell
    Else
    Set oFirstCell = oLastCell.Offset(0, -1).End(xlUp).Offset(0, 1)
    End If
    End If
    Loop
    Application.CutCopyMode = False
    '.Range("J:J").Delete
    '.Range("K:K").SpecialCells(xlCellTypeBlanks).Enti reRow.Delete
    End With
    End Sub

    For some reason it was working on Friday and now it is not. The columns are G and H. I changed the code to reflect column G but when I run the macro, it just puts the numbers in the column beside the numbers. What am I doing wrong? I know it is something goofy because it was working on Friday.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: transpose macro (2003)

    The line

    Set oLastCell = .Range("G65536").End(xlUp)

    should be

    Set oLastCell = .Range("H65536").End(xlUp)

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: transpose macro (2003)

    For some reason on Friday when I used the macro - I was smart and did it right. Over the weekend, I lost my smarts. Thank you.

Posting Permissions

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