I have an Excel file which contains some data in a 2d array.
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– Bryan2012-05-23 14:59:55 +00:00Commented May 23, 2012 at 14:59
-
Exactly sir, so it will be for the line 1 ('tata'), line 2 ('toto') and so onFarmer– Farmer2012-05-23 15:02:20 +00:00Commented May 23, 2012 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– Bryan2012-05-23 15:05:59 +00:00Commented May 23, 2012 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– andy holaday2012-05-23 15:33:38 +00:00Commented May 23, 2012 at 15:33
Add a comment
|
3 Answers
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 Comments
Farmer
And how can I implement this in Excel, since I have never done it before ?
Siddharth Rout
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
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")Using just worksheet tools (no VBA):
Ctrl-F- Find what = ~*
Find AllCtrl-Ato select all the Find resultsClosethe 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
1 Comment
Siddharth Rout
+ 1 For a Non VBA Solution :)
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.
3 Comments
Siddharth Rout
Bryan Looping through the cells is slower than using the inbuilt
.Find and .FindNext. :) Maybe you would like to see this? siddharthrout.wordpress.com/2011/07/14/… Of Course, the speed wouldn't matter if there are very few cells :)Siddharth Rout
You might also want to see this? stackoverflow.com/questions/10714251/…
Bryan
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.