• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

IF value in A1 = 'x', change to 'y'

P

PipBoy808

Member
Lets say I have a range of values in A1:A20 that range from 0-20000. I'd like every '0' value within this range to be replaced by something more practical, such as the word 'CLOSED'.

I've figured out how to do this with custom formatting, but I'd like to learn a VBA solution too.

Thanks!
 
Hi ,

I like your honesty !

Try this , and let me know if you want any changes :

Code: 
Public Sub Enter_Formula()
          Dim wks As Worksheet
          Set wks = ThisWorkbook.Worksheets("Sheet1")      '  Change as required
     
          Dim Rng As Range
          Set Rng = wks.Range("A1:A20")                    '  Change as required
     
          Rng.Value = [IF($A1:$A20 = 0, "CLOSED",$A1:$A20)]
     
          Set Rng = Nothing
          Set wks = Nothing
End Sub

Please note that if you have any formulae in the range A1 through A20 , they will all be overwritten with their values. If you want the formulae to be retained , the code will have to be changed.

Narayan
 
How's this work for you?

Code: 
Sub Switcher()
 
Dim rngCell As Range
 
For Each rngCell In Range("A1:A20")
    If rngCell.Value = 0 Then rngCell = "Closed"
Next rngCell
 
End Sub
 
NARAYANK991 said:
Try this , and let me know if you want any changes :

Code: 
Public Sub Enter_Formula()
          Dim wks As Worksheet
          Set wks = ThisWorkbook.Worksheets("Sheet1")      '  Change as required
 
          Dim Rng As Range
          Set Rng = wks.Range("A1:A20")                    '  Change as required
 
          Rng.Value = [IF($A1:$A20 = 0, "CLOSED",$A1:$A20)]
 
          Set Rng = Nothing
          Set wks = Nothing
End Sub
Narayan
Click to expand...

Oh man. That's the second time this morning that I've taken so long to respond to a question that you've beaten me to the punch, Narayan. But I have to thank you even though this isn't my thread. I've never seen that method you used, and I like it better than my For..Next loop.
 
Both of those worked like a charm. Thanks guys.

Can I ask, what's the significance of this line? Do you need to tell Excel what worksheet this applies to if you're already writing this within the correct worksheet tab in the VBE?

Set wks = ThisWorkbook.Worksheets("Sheet1")
Click to expand...
 
Hi ,

The reason for that statement is that the procedure can be placed in the code section pertaining to Sheet1 , but the macro itself can be executed from within the worksheet , by clicking on the Developer tab , clicking on Macros , and selecting the macro , and pressing the Run button.

When you do this , there is no assurance that your active sheet is Sheet1 ; it may be any other sheet.

To ensure that the actions that follows all act on cells in the Sheet1 tab , it is always good to activate the required sheet , so that all references to cells are with respect to the correct sheet.

You can verify this by commenting out this statement , making any sheet other than Sheet1 the active sheet , and running this macro.

Narayan
 
PipBoy, while my code works, Narayan's employs the best practice of qualifying the worksheet for range references. It's something that I know I should do more often, but usually don't. And since I didn't know whether you would be incorporating that code into a larger macro or just using it alone, I took the quicker route.

Without specifying the sheet name, VBA assumes that the active sheet is what you want to work with, and if you've got code that works on multiple sheets, it's possible that it might run that portion of code on an incorrect worksheet.

I found the article below when I was researching Narayan's use of the square brackets in VBA*. It might seem unrelated to your question at first, but it does offer a few different takes on the issue of qualifying references. I hope that helps.

http://www.ozgrid.com/forum/showthread.php?t=66053

*I had assumed the use of brackets was a shortcut for using Range(), but I learned that it's actually more robust than that.
 
Hi, PipBoy808!
Maybe arriving a bit late but if it's only a matter of display and not of content, why not formatting range like this?:
0;0;"CLOSED";@
Regards!
 
You must log in or register to reply here.
Back
Top