Excel 2010 Macros

Question

我使用excel 2010并创建了一个宏来复制一行文本数据，将其转置到列中，然后在每个值之后添加逗号。现在，我希望相同的宏也在列中找到一个预先确定的值，并将其替换为另一个预先确定的值。以下是我到目前为止写的……

Sub Macro2()
'
' Macro2 Macro
'
' Keyboard Shortcut: Ctrl+q
'
    Range("E2:CL6").Select
    Selection.Copy
    Range("A11").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    x = 1
    Do While Cells(11, x) <> ""
        Cells(11, x).Select
        Set Rng = Range(Selection, Selection.End(xlDown))
        For Each cell In Rng
         cell.Value = cell.Value + ","
        Next
        x = x + 1
    Loop
    Application.CutCopyMode = False
    Columns("B:B").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Cells.Select
    Cells.EntireColumn.AutoFit
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "user id"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "Pin"
    Range("B2").Select
End Sub

Answer 1

这是你的代码(没有.Select更干净一点):

Sub Macro2()
'
' Macro2 Macro
'
' Keyboard Shortcut: Ctrl+q
'
    Range("E2:CL6").Copy
    Range("A11").PasteSpecial Paste:=xlPasteAll, _
                                Operation:=xlNone, _
                                SkipBlanks:=False, _
                                Transpose:=True
    x = 1
    Do While Cells(11, x) <> ""
        Set Rng = Range(Cells(11, x), Cells(11, x).End(xlDown))
        For Each cell In Rng
            cell.Value = CStr(cell.Value & ",")
            cell.Value = Replace(cell.Value, "Fixed Income Research Group", "SS FixedIncomeResearch")
            cell.Value = Replace(cell.Value, "Fixed Income Trading Group", "SS FixedIncomeTrading")
        Next
        x = x + 1
    Loop
    Application.CutCopyMode = False
    Columns("B:B").Insert Shift:=xlToRight, _
                            CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("B:B").Insert Shift:=xlToRight, _
                            CopyOrigin:=xlFormatFromLeftOrAbove
    Cells.EntireColumn.AutoFit
    Range("B1").FormulaR1C1 = "user id"
    Range("C1").FormulaR1C1 = "Pin"
End Sub