使用宏替换Excel文件中的数据

I have an Excel file wich contains some data in a 2d array.

我有一个Excel文件,其中包含一个二维数组中的一些数据。

What I want to do is to create a macro which can replace the asterisk '*' by the header of the column of the table (toto, or tata, or titi).

我想要做的是创建一个宏,可以用表的列标题(toto,或tata,或titi)替换星号'*'。

3 个解决方案

#1

Using just worksheet tools (no VBA):

仅使用工作表工具(无VBA):

• Ctrl-F
• Find what = ~*

找到什么=〜*

• Find All
• Ctrl-A to select all the Find results

Ctrl-A选择所有查找结果

• 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

假设你的标题在第二行,并假设光标落在C列某处(我做了两次,YMMV),输入formula = C $ 2

• Press Ctrl-Enter

#2

Like this?

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

#3

Here is a simple way I came up with.

这是我提出的一种简单方法。

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

Cells(x,y): x refers to row, y refers to column.

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

A more refined range select can be used instead of this basic one to have the code choose the appropriate range.

可以使用更精确的范围选择而不是此基本选择来使代码选择适当的范围。

To implement in excel simply open up the code window and paste this code in the desired macro/subroutine.

要在excel中实现,只需打开代码窗口并将此代码粘贴到所需的宏/子例程中。