?
Solved

MS-Excel Find and Replace

Posted on 2001-06-14
4
Medium Priority
?
168 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 10

Accepted Solution

by:
dij8 earned 100 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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

Today, the web development industry is booming, and many people consider it to be their vocation. The question you may be asking yourself is – how do I become a web developer?
This article was originally published on Monitis Blog, you can check it here . Today it’s fairly well known that high-performing websites and applications bring in more visitors, higher SEO, and ultimately more sales. By the same token, downtime…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
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).
Suggested Courses

770 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