+ Reply to Thread
Results 1 to 7 of 7

Macro to replace cells less than one

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    05-05-2014
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2013
    Posts
    103

    Macro to replace cells less than one

    Hello,

    I am trying to write a macro to replace all cells in column E with a value less than 1 to zero. I have to following macro but it does not work. Any suggestions? Thank you!

    Sub Replace_w_0()
    Dim rng As Range
    For Each rng In Range("e:e")
    If rng.Value < 1 Then rng = 0
    End If
    Next rng
    End Sub
    Thanks,

    Laura
    Last edited by lejanco; 10-07-2014 at 07:58 PM.

  2. #2
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: Macro to replace cells less than one

    Try this:

    Sub Replace_w_0()
    Dim rng As Range
    For Each rng In Range("e1:e" & Range("E" & Rows.Count).End(xlUp).Row)
    If rng.Value < 1 Then   'ultimately this line was the problem
    rng = 0
    End If 
    Next rng
    End Sub
    If rng.Value < 1 Then rng = 0 <--- this alone is ok
    
    If rng.Value <1 Then rng = 0
    End If   <--- like this is not ok
    
    If rng.Value <1 Then
    rng = 0
    End If  <------ this is ok
    Last edited by stnkynts; 10-07-2014 at 07:07 PM.

  3. #3
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2406 Win 11 Home 64 Bit
    Posts
    24,019

    Re: Macro to replace cells less than one

    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

    Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



    (This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  4. #4
    Forum Contributor
    Join Date
    05-05-2014
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2013
    Posts
    103

    Re: Macro to replace cells less than one

    I do not really know what you are referring to?

  5. #5
    Forum Contributor
    Join Date
    05-05-2014
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2013
    Posts
    103

    Re: Macro to replace cells less than one

    I believe I have fixed the problem? can you please remove the other post. Thank you

  6. #6
    Forum Expert Crooza's Avatar
    Join Date
    10-19-2013
    Location
    Hunter Valley, Australia
    MS-Off Ver
    Excel 2003 /7/10
    Posts
    2,082

    Re: Macro to replace cells less than one

    Deleted. OP solved his issue
    Last edited by Crooza; 10-07-2014 at 08:14 PM.
    Happy with my advice? Click on the * reputation button below

  7. #7
    Forum Contributor
    Join Date
    05-05-2014
    Location
    Dallas, TX
    MS-Off Ver
    Excel 2013
    Posts
    103

    Re: Macro to replace cells less than one

    Thanks to everyone for your help! Solved.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Macro to replace numeric cells with another value
    By jeffrys in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 01-22-2014, 09:43 AM
  2. replace formulars in to macro in certain cells
    By stevekirk in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-01-2012, 10:20 AM
  3. [SOLVED] How to use Cells.Replace in macro
    By bafo in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 05-28-2012, 11:01 AM
  4. Macro to look through cells in range and replace
    By uncleslinky in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-21-2011, 04:06 AM
  5. How to insert Cells VALUE (Search and Replace Macro)
    By NatanZ in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 11-30-2010, 01:58 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1