Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 302
  • Last Modified:

Special Paste Transpose in TextBox

Hello all

I would like to know if it's possible to copy a row range in an excel sheet and paste in a textbox1 in Special Paste Transpose son that it will be all 1 under the other?

For example if i copy Range(A3:A25) the value in the textbox1 would be for ex:
A
B
C
D....

and not ABCD.....


That code would go in my form where i have my Textbox1.

Thanks for your help.

0
Wilder1626
Asked:
Wilder1626
  • 5
  • 5
1 Solution
 
Rory ArchibaldCommented:
For example:
    With Me.TextBox1
        .MultiLine = True
        .Text = Join(Application.Transpose(Range("A3:A25").Value), vbCr)
    End With

Open in new window

0
 
Wilder1626Author Commented:
Is there a way to remove the specific range and just use the selection field when i select in the excel sheet and do copy?
0
 
Rory ArchibaldCommented:
Basically:
   With Me.TextBox1
        .MultiLine = True
        .Text = Join(Application.Transpose(Selection.Value), vbCr)
    End With

Open in new window

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.

 
Wilder1626Author Commented:
I have an error on that part of the code
.Text = Join(Application.Transpose(Selection.Value), vbCr)

Run time error 13
Type incompatible.
0
 
Rory ArchibaldCommented:
What did you select?
0
 
Rory ArchibaldCommented:
Actually try this version - it will cope with any range selection (if you select multiple rows and columns, it will use the first column only):
    Dim varData
    If Selection.Rows.Count > 1 Then
        varData = Join(Application.Transpose(Selection.Value), vbCr)
    ElseIf Selection.Columns.Count > 1 Then
        varData = Join(Application.Transpose(Application.Transpose(Selection.Value)), vbCr)
    Else
        varData = Selection.Value
    End If
    With Me.TextBox1
        .MultiLine = True
        .Text = varData
    End With

Open in new window

0
 
Wilder1626Author Commented:
I have select from another excel sheet Range("F11:BL11").copy
0
 
Rory ArchibaldCommented:
Try the revised version - note you do not need to copy, just select the range.
0
 
Wilder1626Author Commented:
oh ok, let me try this
0
 
Wilder1626Author Commented:
Thank you so much

This is perfect.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now