Results 1 to 2 of 2

Thread: Application.Transpose In Excel 2007 and Excel 2010

  1. #1
    Junior Member
    Join Date
    Oct 2012
    Posts
    26
    Rep Power
    0

    Question Application.Transpose In Excel 2007 and Excel 2010

    Hi everyone.

    Im using the code below to change a row in a listbox. As i'm using cellformatting i'm using the Application.Transpose(var) what is working fine in excel 2010 and showing when putting in 3% changing to 3.0% automaticly. When i'm only using = var it put it in as a text and not as a value.

    But when i test the code below in excel 2007 it takes the first value from the row and copy's it in the other 6 columns in the rows.. when can be wrong?

    Code:
    Option Explicit
    Private Sub MenuButton_Click()
        Unload Me
        Menu.Show
    End Sub
    Private Sub CommandButton1_Click()
        Unload Me
    End Sub
    Private Sub UserForm_Initialize()
        Dim x As Long
        Dim k As Long
        Dim j As Long
        Dim rng As Range
       Set rng = Sheets("INPUT WV LISTBOX").Range("A8:H282") '<--If you change the Range
        Rx1 = 8 '<----------------------------------------------------------------------You Must Change this
        With ListBox1
        .Clear
        For k = 1 To rng.Rows.Count 'rows
            .AddItem
            For j = 1 To rng.Columns.Count
            
            .List(k - 1, j - 1) = rng.Cells(k, j).Text 'columns
            Next j, k
        End With
        For j = 1 To 8
            Me.Controls("TextBox" & j).Text = ""
        Next j
    End Sub
    Private Sub CommandButton2_Click()
        Dim var(3 To 8) As String
        Dim j As Long
        Dim x As Long
    
        If Me.ListBox1.ListIndex = -1 Then
            MsgBox "No Data Selected"
            Exit Sub
        End If
        If Me.TextBox1.Value = "" Then Exit Sub
        Rx1 = Rx1 + Me.ListBox1.ListIndex
        With Sheets("INPUT WV LISTBOX")
            For j = 3 To 8
            var(j) = Me.Controls("TextBox" & j).Text
            Next j
            .Cells(Rx1, 3).Resize(, 6).Value = Application.Transpose(var)
        End With
        Call UserForm_Initialize
    End Sub
    Private Sub ListBox1_Click()
        Dim k As Long
        Dim j As Long
        Dim Ctrl As Control
        For Each Ctrl In Me.Controls
            If TypeName(Ctrl) = "TextBox" Then
                k = Me.ListBox1.ListIndex
                For j = 1 To 8
                    Me.Controls("TextBox" & j).Text = Me.ListBox1.List(k, j - 1)
                Next j
            End If
        Next Ctrl
        If Me.TextBox1.Value = "" Then
            MsgBox "Can't Select this Row"
            Exit Sub
        End If
    End Sub

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    Tony, your question isn't very clear. Are you saying that Application.Transpose is working in different ways in Excel 2007 and Excel 2010? You could attach a sample working file, and then clarify the question.

    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNsaS3Lp1
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgR1EPUkhw
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNe_XC-jK
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNPOdiDuv
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgN7AC7wAc
    https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgyBACXgNY4j_cHgH5J4AaABAg.9oTkVdzfqfm 9wlhQrYJP3M
    https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgxYgiEZuS9I3xkjJv54AaABAg
    https://www.youtube.com/watch?v=DVFFApHzYVk&lc=Ugyi578yhj9zShmhuPl4AaABAg
    https://www.youtube.com/watch?v=GqzeFYWjTxI&lc=UgxvxlnuTRWiV6MUZB14AaABAg
    https://www.youtube.com/watch?v=_8i1fVEi5WY&lc=Ugz0ptwE5J-2CpX4Lzh4AaABAg
    https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgxoHAw8RwR7VmyVBUt4AaABAg. 9C-br0lEl8V9xI0_6pCaR9
    https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=Ugz5DDCMqmHLeEjUU8t4AaABAg. 9bl7m03Onql9xI-ar3Z0ME
    https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgxYnpd9leriPmc8rPd4AaABAg. 9gdrYDocLIm9xI-2ZpVF-q
    https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgyjoPLjNeIAOMVH_u94AaABAg. 9id_Q3FO8Lp9xHyeYSuv1I
    https://www.reddit.com/r/windowsxp/comments/pexq9q/comment/k81ybvj/?utm_source=reddit&utm_medium=web2x&context=3
    https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgxYgiEZuS9I3xkjJv54AaABAg
    https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgyBACXgNY4j_cHgH5J4AaABAg.9oTkVdzfqfm 9wlhQrYJP3M
    ttps://www.youtube.com/watch?v=LP9fz2DCMBE
    https://www.youtube.com/watch?v=LP9fz2DCMBE&lc=UgzbPgJUMCztIOQDym14AaABAg
    https://www.youtube.com/watch?v=LP9fz2DCMBE&lc=UgzbPgJUMCztIOQDym14AaABAg. 9wdo_rWgxSH9wdpcYqrvp8
    ttps://www.youtube.com/watch?v=bFxnXH4-L1A
    https://www.youtube.com/watch?v=bFxnXH4-L1A&lc=UgxuODisjo6cvom7O-B4AaABAg.9w_AeS3JiK09wdi2XviwLG
    https://www.youtube.com/watch?v=bFxnXH4-L1A&lc=UgxBU39bTptFznDC1PJ4AaABAg
    ttps://www.youtube.com/watch?v=GqzeFYWjTxI
    https://www.youtube.com/watch?v=GqzeFYWjTxI&lc=UgwJnJDJ5JT8hFvibt14AaABAg
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 11-30-2023 at 02:46 PM.
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

Similar Threads

  1. Test copy Activate Ribbon Tab In Excel 2007 2010 *
    By Excel Fox in forum Test Area
    Replies: 16
    Last Post: 01-22-2019, 05:05 PM
  2. Excel 2003 Classic Menu in Excel 2007-2010
    By Excel Fox in forum Classic Menu
    Replies: 7
    Last Post: 09-10-2014, 10:29 PM
  3. Ribbon Calendar DatePicker Word 2007-2010
    By Excel Fox in forum Download Center
    Replies: 24
    Last Post: 09-23-2013, 09:07 AM
  4. Data Sortin_ MS Excel 2010
    By blake85 in forum Excel Help
    Replies: 2
    Last Post: 03-01-2013, 11:37 AM
  5. Excel Application.OnKey With Parameter
    By Excel Fox in forum Excel Help
    Replies: 0
    Last Post: 11-29-2011, 01:31 PM

Posting Permissions

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