?
Solved

Tranpose With Spaces

Posted on 2012-08-14
8
Medium Priority
?
448 Views
Last Modified: 2012-08-20
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
0
Comment
Question by:Rayne
  • 3
  • 2
  • 2
7 Comments
 

Author Comment

by:Rayne
ID: 38293916
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
 
LVL 14

Expert Comment

by:Tommy Kinard
ID: 38294134
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
 
LVL 46

Assisted Solution

by:aikimark
aikimark earned 1400 total points
ID: 38294728
@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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:Rayne
ID: 38294863
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
 
LVL 46

Accepted Solution

by:
aikimark earned 1400 total points
ID: 38295218
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
 
LVL 14

Assisted Solution

by:Tommy Kinard
Tommy Kinard earned 600 total points
ID: 38296056
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
 

Author Comment

by:Rayne
ID: 38297159
Thank you both for the efforts. This got moving finally. Greatly appreciate your help on this.
Respect
R
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question