Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old April 2nd, 2006, 10:25 PM
Registered User
 
Join Date: Apr 2006
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Help with transpose

Hi, I need to do this, i have a table like this
1 2 3 4
a c d
9 7 5

What I need to do is:
1
2
3
4
a
c
d
9
7
5

So a list comes from a table with data, as you have noticed, on the table there are spaces, that is because when I want the list to be filled, i want it to skip the spaces... Thanks!

 
Old April 6th, 2006, 07:34 AM
Friend of Wrox
 
Join Date: Oct 2003
Location: , , Finland.
Posts: 168
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi,

This code may help you.

Sub Transposing()
Dim Table 'Table variable
Dim Target 'Target cell
Dim i As Integer, k As Integer, j As Integer

Table = Application.InputBox("Enter data", Type:=64)
Set Target = Application.InputBox("Enter target", Type:=8)
j = 0
For i = 1 To UBound(Table, 1)
    For k = 1 To UBound(Table, 2)
        If Table(i, k) <> "" Then
            Target.Cells.Offset(j, 0) = Table(i, k)
            j = j + 1
        End If
    Next k
Next
End Sub

-vemaju
 
Old April 6th, 2006, 02:46 PM
Registered User
 
Join Date: Apr 2006
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks for the code! but can you explain it? Thanks...

 
Old April 7th, 2006, 03:43 AM
Friend of Wrox
 
Join Date: Oct 2003
Location: , , Finland.
Posts: 168
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi,

Here is the same code commented
 Posted - 04/06/2006 : 3:34:14 PM


--------------------------------------------------------------------------------

Hi,

This code may help you.

Sub Transposing()
Dim Table 'Table variable
Dim Target 'Target cell
Dim i As Integer, k As Integer, j As Integer


'*** Gets values to an table array (like excel table)
Table = Application.InputBox("Enter data", Type:=64)
'*** Gets target cell from user
Set Target = Application.InputBox("Enter target", Type:=8)
j = 0
'*** Looping through table array. Ubound 1 is the Upper Bound of first dimension
For i = 1 To UBound(Table, 1)
'*** Looping through table array. Ubound 2 is the Upper Bound of second dimension
    For k = 1 To UBound(Table, 2)
    '*** tests if there is a value
        If Table(i, k) <> "" Then
            '*** writes value to a cell
            Target.Cells.Offset(j, 0) = Table(i, k)
            '*** counter variable for cell offset from target cell
            j = j + 1
        End If
    Next k
Next
End Sub

Hope this helps you to understand how the code works.
You can run the code step by step (F8) and follow the variables in Locals Window View-Locals Windows in editor



-vemaju




Similar Threads
Thread Thread Starter Forum Replies Last Post
Transpose the data yogeshyl Excel VBA 2 September 15th, 2007 11:09 AM
Transpose Tables abc052107 Visual Studio 2005 0 August 23rd, 2007 12:40 AM
How to Transpose this type data jaincafe Access 1 November 27th, 2006 05:58 AM
Transpose Rows JpJoe Access 10 March 17th, 2005 08:15 AM
transfer and transpose sham Excel VBA 4 March 6th, 2005 12:13 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.