Find and Replace bug - Can not look in values

AirMaximus88

New Member
Joined
Mar 13, 2014
Messages
16
Hi everyone!

First time poster here, I do apologise if I contravene any forum rules.

I am having trouble with missing aspects of the Find and Replace function. The Find function allows me to look in Formulas, Values and Comments, but the Find and Replace function only allows me to look in Formulas. I could have sworn that I previously used this function to look in the Values of cells rather than just the Formulas. Any ideas what I've done?

Pictures attached:

Excel_Annoying_2.jpg

Fig 1. Find function allows me to look in Formulas, Values and Comments.


Excel_annoying.jpg

Fig 2. Find and Replace function only allows me to look in Formulas.


Thanks for your help,

Max



P.S. As an afterthought I should probably have mentioned my current version - Excel 2010 Ver. 14.0.7113.5005 (32-bit)
 
this problem is just more of the usual MS arrogance and incompetence.

the option NOT to Replace (or Find) in FORMULAE is obvious: often you will want to replace some TEXT characters that are also used in Formulae. in fact just about every operator used in Formulae could alos be used in text! Eg '-', '*', '!', etc, etc. a simple and critical example that just happened to me (which is why i am here in teh 1st place), i replace ':', and gee guess what happened to all the ranges ion my formulae!!! not pretty!

because MS has basically REFUSED to fix this BUG, the only work-around is basically to MANUALLY select the cells that do not contain any formulae. some users have posted to use Find first and then Replace which will preserve the Find cell results.
 
Upvote 0

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
the only work-around is basically to MANUALLY select the cells that do not contain any formulae

Not quite true.
More efficient would be:
• <F5> or Ctrl+G ie Goto
• Special
• Select contstants
• now do your find and replace (ctrl+H)
It will only do the replace on the selected cells which are the non-formula cells.

If you are using VBA the same principle would apply.
VBA Code:
Activesheet.Cells.SpecialCells(Type:=xlCellType.xlCellTypeConstants).Select

1679803912198.png
 
Upvote 0
too tricky for me! :-)
and basically what SHOULD have happened is for MS to FIX it.

PS it is constantly shocking to me that these huge billionaire software apps are/have been successful with such lousy dysfunctional software. i am thinking particularly of FB and Twitter, but there are plenty of examples, and MS was really the start of that trend. MS have NEVER had the best or most functional software. usually it was f*g pathetic and dysfunctional. even NOW, after so many years, and so much money Outlook is not great software.
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top