Solved

MS-Excel Find and Replace

Posted on 2001-06-14
4
162 Views
Last Modified: 2011-10-03
Hi,


 I have a template stored in Excel.And there a few cells with a unique number(for ex:123456,123444 etc) in them.Is there any way i can modify these numbers to
 <%=a("123456") %>,<%= a("123444")%> ,etc..??I just need to add the delimiters ,quotes and "a" to it.Is there any way i can do it for the whole document.I need to do a quick find and replace for all the numbers instead of going to each and every cell.Any help is appreciated.

Thanks
0
Comment
Question by:scorpio1975
4 Comments
 
LVL 10

Accepted Solution

by:
dij8 earned 25 total points
ID: 6193203
You could runa script that would do it.  As long as you know the cells they are in or if they are all in a column with no breaks.  If you add this script through the Visual basic editor and hit ALT + F8 to run it you should get the result you want.

Sub changecells()
Dim rownumber
rownumber = 2
Do While Not IsEmpty(Cells(rownumber, 1))
  Cells(rownumber, 1) = "<%=a(" & Chr(34) & Cells(rownumber, 1) & Chr(34) & ")%>"
  rownumber = rownumber + 1
Loop
End Sub

This script will run down the A column starting at the second row.  If you wanted to start in a different row change the number to the one you want to start in.  If you want to use a different column then change the number 1 "Cells(rownumber, 1)" to the numeric equivilent of the column letter.  If you want to go across then change the rownumber for a column number.  Therefore, "Cells(rownumber, 1)" becomes "Cells(1, colnumber)" where the 1 is the row number (and remember to change the code so that rownumber becomes colnumber everywhere in the code.
0
 
LVL 15

Expert Comment

by:a.marsh
ID: 6521331
This question has been open for some time and is now in need of wrapping up.

Please DO NOT accept this comment as the answer to the question, it is purely an alert to the fact that this question is still open.

It would be greatly appreciated if any of the participants could comment back here in order to give assistance in what the status of this question is and what should be done with it (delete, 0 PAQ, award points etc).

Kindest Regards

Ant
0
 
LVL 53

Expert Comment

by:COBOLdinosaur
ID: 6806205
It is time to clean this abandoned question up.  

I am putting it on a clean up list for CS.

<recommendation>
points to dij8

</recommendation>

If anyone participating in the Q disagrees with the recommendation,
please leave a comment for the mods.

Cd&
0
 
LVL 5

Expert Comment

by:Netminder
ID: 6808928
Force/accepted by

Netminder
Community Support Moderator
Experts Exchange
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now