Tranpose With Spaces

Hello All,

I have got the code that does this so far – transpose column to a single row of dates. But I have a spacing thing. Need three months followed by a empty cell. For example >>
The transposed numbers need to be in this way – after the first three months, there is a empty cell, then followed by the next three months and an empty cell.

Prior Related Question: http:Q_27827545.html
tranposeAtSpaces.xlsm
spacesHow.png
RayneAsked:
Who is Participating?
 
aikimarkCommented:
Since you weren't using the variable n, I used it for target cell addressing.
Sub myFunc()
    
    
    Dim s As String
    Dim i As Long, n As Long
    Dim X As Variant
    Dim rng, mysortrange, cel As Range
    
    With Range([B2], [B65536].End(xlUp))
          .ClearContents
    End With
    
    'First get unique vlaues
    Range("mysourcerange").Worksheet.Range(Range("mysourcerange").Offset(-1, 0), Range("mysourcerange").Cells(1, 1).End(xlDown)).AdvancedFilter Action:=xlFilterCopy, copytorange:=Sheets(Sheet5.Name).Range("B2"), unique:=True

    Sheets(Sheet5.Name).Range("B2").Delete (xlShiftUp)   'Delete header value

    Call Data_Sort
    
    Set rng = Sheet5.Range("myRange")
    
    rng.ClearContents
     
    Set mysortrange = Range([B2], [B65536].End(xlUp))
    
    With Range([B2], [B65536].End(xlUp))
        X = .Value
        n = 0
        
        Set rng = ActiveSheet.Range("myRange")
        
        For i = LBound(X) To UBound(X)
            n = n + 1
            rng.Cells(1, n).Value = X(i, 1)
            If (Month(X(i, 1)) Mod 3) = 0 Then  'extra increment after end of quarter
                n = n + 1
            End If
        Next
    
    End With

End Sub

Open in new window

0
 
RayneAuthor Commented:
something like this
   For i = LBound(X) To UBound(X)
       
            If i = 4 Or i = 8 Or i = 12 Then
                rng.Cells(i).Offset(, 1).Value = X(i, 1)
                i = i + 1
            Else
                rng.Cells(i).Value = X(i, 1)
            End If
           
        Next


I am still not getting it
0
 
Tommy KinardCommented:
This works
For i = LBound(X) To UBound(X)
            
            If LnCnt = 3 Then
                Ad = Ad + 1
                LnCnt = 0
            End If
            rng.Cells(i + Ad).Value = X(i, 1)
            LnCnt = LnCnt + 1
        Next

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
aikimarkCommented:
@Rayne

I advise against changing an iteration variable inside the For loop.  If the Dragontooth technique doesn't work, I'll post some alternatives, based on your problems.  The two alternatives involve a separate variable for the target address, similar to the Dragontooth adjustment, and a post transpose tweak, inserting empty columns at three month intervals.

Note: The EE question notifications system is having a problem, so I might not know when you post a comment.  I'm not ignoring you.
0
 
RayneAuthor Commented:
Hello Everyone :)

Dragontooth thank you for your code. I noticed when I call the macro, i get a last three months on the next line, instead of the same line as other months. I am not sure why thats doing it. See image attached.

 Aikimark - Sure, Please go ahead and make the suggestions as you indicated. I will go with the best practices as much has possible

Thank you all

Respectfully,
R
tranposeNextLine.png
0
 
Tommy KinardCommented:
The range "myrange" is defined with 3 columns too few so the macro goes to the next line below the current range.

Redefine the range "myrange" to include 3 extra columns and then it will do as you expect. :)

I have attached the revised file for your review.
tranposeAtSpaces-Redefined-myran.xlsm
0
 
RayneAuthor Commented:
Thank you both for the efforts. This got moving finally. Greatly appreciate your help on this.
Respect
R
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.