Hello All,

I am using Office XP and have a problem.

I have a workbook with many sheets where I had defined global range
names for eg

xxxxData (xxxx = variable lengths of Alphabets from A to Z)

Now for some reason I need to change it to local range names for eg
xxxx!Data

and also replace "" (double quotes) used with an IF formula to
"NA"

The range names are used in various Vlookup Formulas.

Is there a macro to search each formula and change xxxxData to
xxxx!Data or simply replace the word Data with !Data and also replace
"" to "NA"..on all the sheets in the workbook

I searched "" and replaced it with "NA" which worked with
Replace All.

I tried the following with Find and Replace:
Find: Data
Replace with: !Data

but I cannot use Replace All ...for some reason it gives me error and
have to do it one by one which works...but with so many sheets doing
manually is taking me a long time to do it.

Any help would be appreciated

TIA

Rashid

I recorded this in Excel 2000

Selection.SpecialCells(xlCellTypeFormulas, 23).Select

This finds only cells with formulas

You cound loop through each worksheet
select specialcells
than loop through each cell in selection
Compare cell formula for what you are searching to change
change formula

--
steveB

Remove "AYN" from email to respond
"prkhan56" wrote in message
oups.com...
Hello All,

I am using Office XP and have a problem.

I have a workbook with many sheets where I had defined global range
names for eg

xxxxData (xxxx = variable lengths of Alphabets from A to Z)

Now for some reason I need to change it to local range names for eg
xxxx!Data

and also replace "" (double quotes) used with an IF formula to
"NA"

The range names are used in various Vlookup Formulas.

Is there a macro to search each formula and change xxxxData to
xxxx!Data or simply replace the word Data with !Data and also replace
"" to "NA"..on all the sheets in the workbook

I searched "" and replaced it with "NA" which worked with
Replace All.

I tried the following with Find and Replace:
Find: Data
Replace with: !Data

but I cannot use Replace All ...for some reason it gives me error and
have to do it one by one which works...but with so many sheets doing
manually is taking me a long time to do it.

Any help would be appreciated

TIA

Rashid

Hello Steve,
Thanks for your reply. But I am a newbie and I dont know how make the
coding..thats why I was searching for help in the groups.

Thanks

Rashid Khan

STEVE BELL wrote:
I recorded this in Excel 2000

Selection.SpecialCells(xlCellTypeFormulas, 23).Select

This finds only cells with formulas

You cound loop through each worksheet
select specialcells
than loop through each cell in selection
Compare cell formula for what you are searching to change
change formula

--
steveB

Remove "AYN" from email to respond
"prkhan56" wrote in message
oups.com...
Hello All,

I am using Office XP and have a problem.

I have a workbook with many sheets where I had defined global range
names for eg

xxxxData (xxxx = variable lengths of Alphabets from A to Z)

Now for some reason I need to change it to local range names for eg
xxxx!Data

and also replace "" (double quotes) used with an IF formula to
"NA"

The range names are used in various Vlookup Formulas.

Is there a macro to search each formula and change xxxxData to
xxxx!Data or simply replace the word Data with !Data and also replace
"" to "NA"..on all the sheets in the workbook

I searched "" and replaced it with "NA" which worked with
Replace All.

I tried the following with Find and Replace:
Find: Data
Replace with: !Data

but I cannot use Replace All ...for some reason it gives me error and
have to do it one by one which works...but with so many sheets doing
manually is taking me a long time to do it.

Any help would be appreciated

TIA

Rashid

This works in Excel 2000 (not sure how it will work in office XP)

The code defines the range containing cells with formulas
Yhan it changes all cells with formulas.
change "i" & "z" to what you need.

You can do it cell by cell with the second code.

============================
Dim rng As Range, wks As Worksheet,

For Each wks In ThisWorkbook.Worksheets

On Error Resume Next
Set rng = wks.Cells.SpecialCells(xlCellTypeFormulas, 23)
If Not rng Is Nothing Then
rng.Replace What:="i", Replacement:="z", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End If
Set rng = Nothing
Next
======================================
Dim cel as rng rng As Range, wks As Worksheet,

For Each wks In ThisWorkbook.Worksheets

On Error Resume Next
Set rng = wks.Cells.SpecialCells(xlCellTypeFormulas, 23)
If Not rng Is Nothing Then
For each cel in rng
cel.Replace What:="i", Replacement:="z"
End If
End If
Set rng = Nothing
Next
==========================

hope this is what you are looking for.
--
steveB

Remove "AYN" from email to respond
"prkhan56" wrote in message
ups.com...
Hello Steve,
Thanks for your reply. But I am a newbie and I dont know how make the
coding..thats why I was searching for help in the groups.

Thanks

Rashid Khan

STEVE BELL wrote:
I recorded this in Excel 2000

Selection.SpecialCells(xlCellTypeFormulas, 23).Select

This finds only cells with formulas

You cound loop through each worksheet
select specialcells
than loop through each cell in selection
Compare cell formula for what you are searching to change
change formula

--
steveB

Remove "AYN" from email to respond
"prkhan56" wrote in message
oups.com...
Hello All,

I am using Office XP and have a problem.

I have a workbook with many sheets where I had defined global range
names for eg

xxxxData (xxxx = variable lengths of Alphabets from A to Z)

Now for some reason I need to change it to local range names for eg
xxxx!Data

and also replace "" (double quotes) used with an IF formula to
"NA"

The range names are used in various Vlookup Formulas.

Is there a macro to search each formula and change xxxxData to
xxxx!Data or simply replace the word Data with !Data and also replace
"" to "NA"..on all the sheets in the workbook

I searched "" and replaced it with "NA" which worked with
Replace All.

I tried the following with Find and Replace:
Find: Data
Replace with: !Data

but I cannot use Replace All ...for some reason it gives me error and
have to do it one by one which works...but with so many sheets doing
manually is taking me a long time to do it.

Any help would be appreciated

TIA

Rashid

Hi Steve,
Thanks for the code and help and it worked for replacing DATA with
!DATA (preceded by an exclamation mark)

But I have a slight problem...I wish to replace "" (double quotes)
with "NA" used in an IF formula to display Blank or NA...then I
cannot do it

I tried to replace "i" with """" and "z" with
"NA"... I did not succeed

I wish to replace double quotes ("") with "NA" (NA with double
quotes)

Any idea?

Thanks again
Rashid

This gets tricky because of all the quotes. (sometimes confuses the h...
out of me)...

The code below is close (but not perfect)
Good luck...

To put text in a cell - you must inclose it in quotes

cel = "my text"

To put a quote in a cell
cel = """
To put a double quote in a cell
cel = """""" <<< yep - 6 quotes

if the cell contains quotes then look for """my text"""
to put in quotes around text use """my text"""

You need to determine whether the cell really has quotes or needs quotes
if there is NA in a cell than cell = "NA"

To get a better idea - record putting text into cells.

If you run the following code - put a watch on cel and step through the
code.

Dim cel As Range
For Each cel In Range("a1:A10")
If cel = """i""" Then
cel = """"""
ElseIf cel = """z""" Then
cel = """NA"""
ElseIf cel = """" Then
cel = """NA"""
End If
Next


--
steveB

Remove "AYN" from email to respond
"prkhan56" wrote in message
ups.com...
Hi Steve,
Thanks for the code and help and it worked for replacing DATA with
!DATA (preceded by an exclamation mark)

But I have a slight problem...I wish to replace "" (double quotes)
with "NA" used in an IF formula to display Blank or NA...then I
cannot do it

I tried to replace "i" with """" and "z" with
"NA"... I did not succeed

I wish to replace double quotes ("") with "NA" (NA with double
quotes)

Any idea?

Thanks again
Rashid