Hi,
I want to replace all numbers in the worksheet which are greater than 200 with 200.
I mean all numbers greater than 200 should be set to 200.
values are not all in the same column or same row, they are spread throughout the worksheet.
I tried using the Find and Replace, but it can replace only a specified value with only another specified value.
I want to use some kind of a filter which will choose all numbers greater than 200, and make them 200.
Please help me, thanks!
Hi,
Try this code
To enter, press ALT & F11 to open the VBA editor.
Please Login or Register to view this content.
Insert > Module > Paste the above code
Close the VBA editor
In excel select the cells you wish to run the code on.
Go to tools > macros > run macro (i think - not able to run 2003 currently)
Rule 1: Never merge cells
Rule 2: See rule 1
"Tomorrow I'm going to be famous. All I need is a tennis racket and a hat".
You mention that you tried find and replace
If there are no numbers over 299, and we are talking about whole numbers (integers), then you could try this
Find what:= 2**
Replace with:= 200
Options >> check the box Match entire cell contents
Replace All
This is only meant for your information, not as a substitute for sweeps answer.
Last edited by Marcol; 11-04-2010 at 10:58 AM.
If you need any more information, please feel free to ask.
However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....
Also
اس کی مدد کرتا ہے اگر
شکریہ کہنے کے لئے سٹار کلک کریں
If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.
Hey , I want to use the same code as above but I want to check if rcell with one line above and one column in left is =0Please Login or Register to view this content.
Can someone help me?
Regards, TMSPlease Login or Register to view this content.
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Hello paala, and welcome to the forum.
Unfortunately you have inadvertently broken one of the forum rules. Please read the following and make the necessary change. Thanks.
Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.
Originally Posted by sweep
Hi,
Try this code
To enter, press ALT & F11 to open the VBA editor.Please Login or Register to view this content.
Insert > Module > Paste the above code
Close the VBA editor
In excel select the cells you wish to run the code on.
Go to tools > macros > run macro (i think - not able to run 2003 currently)
Ok this worked...but, there's one problem. I need to replace every # in my entire worksheet say with 10,000 to create a dummy for someone i cant reveal top financial #s to yet wants to see how my spreadsheet works. The problem is, it replaced all the text wtih 10,000 too? Is there a way to only replace #'s greater than 0 wtih 10,000?
Nvm i figured it out on my own...
here's the code if anyone needs it
Please Login or Register to view this content.
Last edited by arlu1201; 06-07-2013 at 01:54 AM.
usansay18,
Welcome to the forum.
I have added code tags to your post. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. In order to put code tags, either type [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] at the end of it, OR you can highlight your code and click the # icon at the top of your post window.
If I have helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
You'll probably never read this since it's 4 years ago but you helped me out so much here. Thanks!
You'll probably never read this since it's 4 years ago but you helped me out so much here. Thanks!Hi,
Try this code
Select Code copy to clipboard
Sub Two_Hundred()
Dim rCell As Range
For Each rCell In Selection
If rCell.Value > 200 Then rCell.Value = 200
Next rCell
End Sub
To enter, press ALT & F11 to open the VBA editor.
Insert > Module > Paste the above code
Close the VBA editor
In excel select the cells you wish to run the code on.
Go to tools > macros > run macro (i think - not able to run 2003 currently)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks