Excel VBA用字符转置 [英] Excel VBA transpose with characters
问题描述
例如
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屋!