Question

我有一个Excel文件包含在 2D 阵列中的一些数据。

"https://i.sstatic.net/TtFho.png" alt="Excel文档"/>

我想做的是创建一个宏, 能够替换星号 * (to, atta, etta, etti), 替换表格列的页眉。

Answer 1

仅使用工作表工具( 没有 VBA) :

Ctrl-F
Find what = ~*
Find All
Ctrl-A to select all the Find results
Close the Find dialog
Assuming your headers in row two, and assuming the cursor lands in column C somewhere (mine did twice, YMMV), type formula =C$2
Press Ctrl-Enter

Answer 2

像这样?

Option Explicit

Sub Sample()
    Dim oRange As Range, aCell As Range, bCell As Range
    Dim ws As Worksheet
    Dim ExitLoop As Boolean

    On Error GoTo Whoa

     ~~> Change this to the relevant sheet name
    Set ws = Worksheets("Sheet1")

    Set oRange = ws.Cells

    Set aCell = oRange.Find(What:="~*", LookIn:=xlValues, _
                LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=False, SearchFormat:=False)

    If Not aCell Is Nothing Then
        Set bCell = aCell
         ~~> Assuming that the headers are in row 2
        aCell.Value = Cells(2, aCell.Column)
        Do While ExitLoop = False
            Set aCell = oRange.FindNext(After:=aCell)

            If Not aCell Is Nothing Then
                If aCell.Address = bCell.Address Then Exit Do
                 ~~> Assuming that the headers are in row 2
                aCell.Value = Cells(2, aCell.Column)
            Else
                ExitLoop = True
            End If
        Loop
    End If
    Exit Sub
Whoa:
    MsgBox Err.Description
End Sub

Answer 3

我有一个简单的方法想出。

i = 3
While Cells(2, i).Value <> ""
    Range(Cells(3, i), Cells(6, i)).Select

    Selection.Replace What:="~*", Replacement:=Cells(2, i).Value, LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
    i = i + 1
Wend

单元格(x,y):x指行,y指列。

可以使用一个更精细的范围选择, 而不是此基本范围, 让代码选择合适的范围。

执行优异功能只需打开代码窗口,并将该代码粘贴在所需的宏/次常规中。

友情链接