Replace data by VALUE or COMMENTS not availabel in Excel 2003 ???
 

I upgraded to Office 2003 and noticed that the option to find and replace
data by VALUE or COMMENTs is not available to me anymore. I can only look by
FORMULAS. Is it something that I have left out when doing the Advanced
Options Install or is it no longer available?

Click Options to further define your search, and then do any of the following:
In the Within box, select Sheet or Workbook to search a worksheet or an
entire workbook.

The steps used to be....
1. In the Search box, click By Rows or By Columns.
2. In the Look in box, click Formulas, Values, or Comments.

Now number 2 only shows FORMULAS.

You can still use Find (not replace) to search through values, formulas,
comments. (Make sure you're on the Find Tab.

Methinks your memory is fading. I don't think that this has changed in a long
time.

MCP wrote:

I upgraded to Office 2003 and noticed that the option to find and replace
data by VALUE or COMMENTs is not available to me anymore. I can only look by
FORMULAS. Is it something that I have left out when doing the Advanced
Options Install or is it no longer available?

Click Options to further define your search, and then do any of the following:
In the Within box, select Sheet or Workbook to search a worksheet or an
entire workbook.

The steps used to be....
1. In the Search box, click By Rows or By Columns.
2. In the Look in box, click Formulas, Values, or Comments.

Now number 2 only shows FORMULAS.

--

Dave Peterson

Dear Dave,

Thanks for the reply. My memory IS fading but I am sure that really doesn't
have anything to do with the problem I have.

Before posting my question, I looked at the Help menu in Office 2003. If you
go to help and do a search for "Find or replace text and numbers on a
worksheet " you will see that it shows the steps to do that and in step 4
specifically says...

4. Click Options to further define your search, and then do any of the
following:
In the Within box, select Sheet or Workbook to search a worksheet or an
entire workbook.
In the Search box, click By Rows or By Columns.
In the Look in box, click Formulas, Values, or Comments.
To search for case-sensitive content, select the Match case check box.
To search for cells that contain just the characters that you typed in the
Find what box, select the Match entire cell contents check box.

I think it has to be something else, but thanks for taking the time to write!

Marcelo.


"Dave Peterson" wrote:

You can still use Find (not replace) to search through values, formulas,
comments. (Make sure you're on the Find Tab.

Methinks your memory is fading. I don't think that this has changed in a long
time.

MCP wrote:

I upgraded to Office 2003 and noticed that the option to find and replace
data by VALUE or COMMENTs is not available to me anymore. I can only look by
FORMULAS. Is it something that I have left out when doing the Advanced
Options Install or is it no longer available?

Click Options to further define your search, and then do any of the following:
In the Within box, select Sheet or Workbook to search a worksheet or an
entire workbook.

The steps used to be....
1. In the Search box, click By Rows or By Columns.
2. In the Look in box, click Formulas, Values, or Comments.

Now number 2 only shows FORMULAS.

--

Dave Peterson

Dear Dave,

My memory IS fading, but I really think it doesn't have anything to do with
my problem. If you go to the help menu in Office 2003 and search for "Find or
replace text and numbers on a worksheet " it will tell you that the option is
available, as it always was. Look at Step 4...

Click Options to further define your search, and then do any of the following:
4. In the Within box, select Sheet or Workbook to search a worksheet or an
entire workbook.
In the Search box, click By Rows or By Columns.
In the Look in box, click Formulas, Values, or Comments.
To search for case-sensitive content, select the Match case check box.
To search for cells that contain just the characters that you typed in the
Find what box, select the Match entire cell contents check box.

Thanks for taking the time to answer, though. I will continue to try and
figure this out!

Marcelo.



"Dave Peterson" wrote:

You can still use Find (not replace) to search through values, formulas,
comments. (Make sure you're on the Find Tab.

Methinks your memory is fading. I don't think that this has changed in a long
time.

MCP wrote:

I upgraded to Office 2003 and noticed that the option to find and replace
data by VALUE or COMMENTs is not available to me anymore. I can only look by
FORMULAS. Is it something that I have left out when doing the Advanced
Options Install or is it no longer available?

Click Options to further define your search, and then do any of the following:
In the Within box, select Sheet or Workbook to search a worksheet or an
entire workbook.

The steps used to be....
1. In the Search box, click By Rows or By Columns.
2. In the Look in box, click Formulas, Values, or Comments.

Now number 2 only shows FORMULAS.

--

Dave Peterson

Try clicking on the Replace tab first. Then look at the options for that "look
in" box.

And when you choose Formulas in the replace dialog, you'll get both the values
and formulas.

If you want to get comments, you could use a macro...

Kind of like:

Option Explicit
Sub testme01()

Dim FoundCell As Range
Dim FindWhat As String
Dim WithWhat As String

FindWhat = "ASDF"
WithWhat = "qwer"

Do
Set FoundCell = ActiveSheet.Cells.Find(What:=FindWhat, _
After:=ActiveCell, _
LookIn:=xlComments, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=True)

If FoundCell Is Nothing Then
Exit Do
Else
FoundCell.Comment.Text _
application.substitute(FoundCell.Comment.Text, _
FindWhat, WithWhat)
End If
Loop

End Sub

application.substitute is case sensitive--so you'll have to match case.

MCP wrote:

Dear Dave,

Thanks for the reply. My memory IS fading but I am sure that really doesn't
have anything to do with the problem I have.

Before posting my question, I looked at the Help menu in Office 2003. If you
go to help and do a search for "Find or replace text and numbers on a
worksheet " you will see that it shows the steps to do that and in step 4
specifically says...

4. Click Options to further define your search, and then do any of the
following:
In the Within box, select Sheet or Workbook to search a worksheet or an
entire workbook.
In the Search box, click By Rows or By Columns.
In the Look in box, click Formulas, Values, or Comments.
To search for case-sensitive content, select the Match case check box.
To search for cells that contain just the characters that you typed in the
Find what box, select the Match entire cell contents check box.

I think it has to be something else, but thanks for taking the time to write!

Marcelo.

"Dave Peterson" wrote:

You can still use Find (not replace) to search through values, formulas,
comments. (Make sure you're on the Find Tab.

Methinks your memory is fading. I don't think that this has changed in a long
time.

MCP wrote:

I upgraded to Office 2003 and noticed that the option to find and replace
data by VALUE or COMMENTs is not available to me anymore. I can only look by
FORMULAS. Is it something that I have left out when doing the Advanced
Options Install or is it no longer available?

Click Options to further define your search, and then do any of the following:
In the Within box, select Sheet or Workbook to search a worksheet or an
entire workbook.

The steps used to be....
1. In the Search box, click By Rows or By Columns.
2. In the Look in box, click Formulas, Values, or Comments.

Now number 2 only shows FORMULAS.

--

Dave Peterson


--

Dave Peterson

RIGHT ON THE MONEY!

Although the Replace Option only shows FORMULAS, it does in fact replace
BOTH Values and Formulas. That solves the issue all together, though I will
keep the macro for an automated version of it.

THANK YOU VERY MUCH!

Marcelo.

-------------------------------------------------------------------------------------------------------

"Dave Peterson" wrote:

Try clicking on the Replace tab first. Then look at the options for that "look
in" box.

And when you choose Formulas in the replace dialog, you'll get both the values
and formulas.

If you want to get comments, you could use a macro...

Kind of like:

Option Explicit
Sub testme01()

Dim FoundCell As Range
Dim FindWhat As String
Dim WithWhat As String

FindWhat = "ASDF"
WithWhat = "qwer"

Do
Set FoundCell = ActiveSheet.Cells.Find(What:=FindWhat, _
After:=ActiveCell, _
LookIn:=xlComments, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=True)

If FoundCell Is Nothing Then
Exit Do
Else
FoundCell.Comment.Text _
application.substitute(FoundCell.Comment.Text, _
FindWhat, WithWhat)
End If
Loop

End Sub

application.substitute is case sensitive--so you'll have to match case.

MCP wrote:

Dear Dave,

Thanks for the reply. My memory IS fading but I am sure that really doesn't
have anything to do with the problem I have.

Before posting my question, I looked at the Help menu in Office 2003. If you
go to help and do a search for "Find or replace text and numbers on a
worksheet " you will see that it shows the steps to do that and in step 4
specifically says...

4. Click Options to further define your search, and then do any of the
following:
In the Within box, select Sheet or Workbook to search a worksheet or an
entire workbook.
In the Search box, click By Rows or By Columns.
In the Look in box, click Formulas, Values, or Comments.
To search for case-sensitive content, select the Match case check box.
To search for cells that contain just the characters that you typed in the
Find what box, select the Match entire cell contents check box.

I think it has to be something else, but thanks for taking the time to write!

Marcelo.

"Dave Peterson" wrote:

You can still use Find (not replace) to search through values, formulas,
comments. (Make sure you're on the Find Tab.

Methinks your memory is fading. I don't think that this has changed in a long
time.

MCP wrote:

I upgraded to Office 2003 and noticed that the option to find and replace
data by VALUE or COMMENTs is not available to me anymore. I can only look by
FORMULAS. Is it something that I have left out when doing the Advanced
Options Install or is it no longer available?

Click Options to further define your search, and then do any of the following:
In the Within box, select Sheet or Workbook to search a worksheet or an
entire workbook.

The steps used to be....
1. In the Search box, click By Rows or By Columns.
2. In the Look in box, click Formulas, Values, or Comments.

Now number 2 only shows FORMULAS.

--

Dave Peterson


--

Dave Peterson

Replace data by VALUE or COMMENTS not availabel in Excel 2003
 

I seem to be having a similar problem with REPLACE. I am unable to replace a
date. With FIND, I can find it if I choose "Values", but not "Formulas"
which is the only choice available under REPLACE. Are there any ADD-INS that
offers this?

"Marcelo Pignatta" wrote:

Dear Dave,

My memory IS fading, but I really think it doesn't have anything to do with
my problem. If you go to the help menu in Office 2003 and search for "Find or
replace text and numbers on a worksheet " it will tell you that the option is
available, as it always was. Look at Step 4...

Click Options to further define your search, and then do any of the following:
4. In the Within box, select Sheet or Workbook to search a worksheet or an
entire workbook.
In the Search box, click By Rows or By Columns.
In the Look in box, click Formulas, Values, or Comments.
To search for case-sensitive content, select the Match case check box.
To search for cells that contain just the characters that you typed in the
Find what box, select the Match entire cell contents check box.

Thanks for taking the time to answer, though. I will continue to try and
figure this out!

Marcelo.



"Dave Peterson" wrote:

You can still use Find (not replace) to search through values, formulas,
comments. (Make sure you're on the Find Tab.

Methinks your memory is fading. I don't think that this has changed in a long
time.

MCP wrote:

I upgraded to Office 2003 and noticed that the option to find and replace
data by VALUE or COMMENTs is not available to me anymore. I can only look by
FORMULAS. Is it something that I have left out when doing the Advanced
Options Install or is it no longer available?

Click Options to further define your search, and then do any of the following:
In the Within box, select Sheet or Workbook to search a worksheet or an
entire workbook.

The steps used to be....
1. In the Search box, click By Rows or By Columns.
2. In the Look in box, click Formulas, Values, or Comments.

Now number 2 only shows FORMULAS.

--

Dave Peterson