Excel VBA用字符转置 [英] Excel VBA transpose with characters

查看:242
本文介绍了Excel VBA用字符转置的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有一个范围有918个单元格(A1:A918)。每个单元格有一个字符串。我需要使用vba代码来围绕每个单元格的引号(),并在末尾添加一个逗号(,)。然后转移列表。我不能将这些字符添加到当前列表中。



例如

  CURRENT LIST(Sheet1)
单元格A1:Bob
单元格A2:简
单元格A3:丹
单元格A4:菲尔
单元格A5:吉米


RESULT(Sheet2)
单元格A1:Bob,
单元格B1:简,
单元格C1:丹,
单元格D1:菲尔,
单元格E1:吉米,

它将如下所示:Bob,Jane,Dan,Phil,Jimmy

我知道使用以下方式进行转置:


$ b $复制
工作表(Sheet2)范围(A1)。PasteSpecial Transpose(A1) := True

但是我不知道如何将字符串包含到每个单元格中。可以帮助吗?

解决方案

您可以粘贴到目标表单元格A1,然后使用文本到列方法吗?
http://msdn.microsoft.com/en-us /library/office/ff193593.aspx



编辑:
也许我不明白这个问题。尝试像

  Sub transpose()
Dim rng As Range
Dim ws As Worksheet
最后一个作为范围

设置ws = ActiveSheet
设置last = ws.Cells(Rows.Count,A)。End(xlUp)
设置rng = ws。范围(A1,最后)

对于每个单元格在rng
Dim hold As String
hold =
hold = hold + cell.Value
hold = hold ++,
cell.Value = hold
下一个单元格

rng.Copy
ActiveWorkbook.Sheets 2).Range(A1)。PasteSpecial transpose:= True

End Sub


I have a range with 918 cells (A1:A918). Each cell has one string. I need vba code to surround each cell with quotes (" ") and add a comma (,) at the end. Then Transpose the list. I cannot add those characters to the current list either.

For example

CURRENT LIST  (Sheet1)
Cell A1: Bob
Cell A2: Jane
Cell A3: Dan
Cell A4: Phil
Cell A5: Jimmy


RESULT (Sheet2)
Cell A1: "Bob",
Cell B1: "Jane",
Cell C1: "Dan", 
Cell D1: "Phil",
Cell E1: "Jimmy",

It will appear like this: "Bob", "Jane", "Dan", "Phil", "Jimmy"

I know to use the following to transpose:

Worksheets("Sheet1").Range("A1:A5").Copy
Worksheets("Sheet2").Range("A1").PasteSpecial Transpose:=True

But I cannot figure out how to include the strings into each cell. Can anyone help?

解决方案

Can you paste to cell A1 on the destination sheet then use the Text to columns method? http://msdn.microsoft.com/en-us/library/office/ff193593.aspx

Edit: Maybe I didn't understand the question. Try something like

Sub transpose()
Dim rng As Range
Dim ws As Worksheet
Dim last As Range

Set ws = ActiveSheet   
Set last = ws.Cells(Rows.Count, "A").End(xlUp)
Set rng = ws.Range("A1", last)

For Each cell In rng
    Dim hold As String
    hold = """"
    hold = hold + cell.Value
    hold = hold + """" + ", "
    cell.Value = hold
Next cell

rng.Copy
ActiveWorkbook.Sheets(2).Range("A1").PasteSpecial transpose:=True

End Sub

这篇关于Excel VBA用字符转置的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆