Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

MS-Excel Find and Replace

Posted on 2001-06-14
4
Medium Priority
?
171 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Dramatic changes are revolutionizing how we build and use technology. Every company is automating, digitizing, and modernizing operations. We need a better, more connected way to work together as teams so we can harness the insights from our system…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
The viewer will get a basic understanding of what section 508 compliance can entail, learn about skip navigation links, alt text, transcripts, and font size controls.
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.
Suggested Courses

610 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