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
Medium Priority
171 Views
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
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

LVL 10

Accepted Solution

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

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

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,

Cd&
0

LVL 5

Expert Comment

ID: 6808928
Force/accepted by

Netminder
Community Support Moderator
Experts Exchange
0

## Featured Post

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â€¦