Replace data in an Excel file using macros

Question

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

Excel document

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).

Replace asterisk with what exactly? I'm unclear as to what you are wanting. Do you simply want to replace every asterisk with the header name for that column? — Bryan, May 23 '12 at 14:59
Exactly sir, so it will be for the line 1 ('tata'), line 2 ('toto') and so on — Wassim AZIRAR, May 23 '12 at 15:02
So just for extra clarity, you want the data after the macro to look like: first line of data (1 38 345 tata) second line (2 toto 367 345) etc.? — Bryan, May 23 '12 at 15:05
Just curious, why ask for a macro and tag this with VBA if you don't know how to implement such a solution? Did you really mean "macro"? — andy holaday, May 23 '12 at 15:33

score 5 · Answer 1 · answered May 23 '12 at 15:03

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

And how can I implement this in Excel, since I have never done it before ? — Wassim AZIRAR, May 23 '12 at 15:06
You paste this code in a module. Replace "Sheet1" in the above code with the relevant sheetname which has the data. Also change `2` in `aCell.Value = Cells(2, aCell.Column)` to whatever row the headers are in and finally run the macro :) — Siddharth Rout, May 23 '12 at 15:08
Also I am using `Set oRange = ws.Cells` for the complete sheet. If you want you can change it to the relevant table range. For example `Set oRange = ws.Range("B2:E6")` — Siddharth Rout, May 23 '12 at 15:12

score 3 · Accepted Answer · answered May 23 '12 at 15:38

3

Using just worksheet tools (no 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

answered May 23 '12 at 15:38

andy holaday

2,282
1
17
25

1

+ 1 For a Non VBA Solution :) – Siddharth Rout May 23 '12 at 15:43

score 0 · Answer 3 · answered May 23 '12 at 15:21

0

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.

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.

answered May 23 '12 at 15:21

Bryan

1,851
11
33
56

1

Bryan Looping through the cells is slower than using the inbuilt `.Find` and `.FindNext`. :) Maybe you would like to see this? http://siddharthrout.wordpress.com/2011/07/14/find-and-findnext-in-excel-vba/ Of Course, the speed wouldn't matter if there are very few cells :) – Siddharth Rout May 23 '12 at 15:24
You might also want to see this? http://stackoverflow.com/questions/10714251/excel-macro-not-using-select – Siddharth Rout May 23 '12 at 15:30
Speed will in this case be based on the number of columns, assuming he doesn't have a huge number of columns the difference between our run-times will likely be negligible. And while I am sure .Find is in many ways better than my admittedly simple suggestion, I prefer to post things that are easily readable and understandable. My answer was simply taking how Excel does its "Find and Replace" feature, and adding loop for each column. Again the intent of my answer was to be basic and simple. – Bryan May 23 '12 at 16:04

Replace data in an Excel file using macros

3 Answers3

Linked