Solved

MS-Excel Find and Replace

Posted on 2001-06-14
4
161 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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Deprecated and Headed for the Dustbin By now, you have probably heard that some PHP features, while convenient, can also cause PHP security problems.  This article discusses one of those, called register_globals.  It is a thing you do not want.  …
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…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The is a quite short video tutorial. In this video, I'm going to show you how to create self-host WordPress blog with free hosting service.

895 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

13 Experts available now in Live!

Get 1:1 Help Now