Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find and replace data in an Excel chart object | Charts and Charting in Excel | |||
Help with data not getting plotted | Excel Discussion (Misc queries) | |||
Help with data not getting plotted | Charts and Charting in Excel | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions | |||
Charting data ranges that change | Charts and Charting in Excel |