Excel 2010 Macro: Replace all cells containing number greater than AND smaller than

Question

I have a large excel sheet in which I want to replace several number ranges (all within the same range of cells). For example, I want to replace all numbers greater than -5 and smaller than 5 with the number 0. All numbers greater than 5 and smaller than 10 should be replaced with 1, all numbers greater than -10 and smaller than -5 should be replaced with -1, and so forth.

I tried using this formula

Sub replace()
    Dim rng As Range

    For Each rng In Range("B2:AI40")
        If IsNumeric(rng) < 5 And IsNumeric(rng) > -5 Then
            rng = 0
        End If
    Next rng
End Sub

which doesn't quite do the trick as it replace everything with 0. Similarly, this solution doesn't do the trick either. How can I modify either formula to suit my needs, or is such a replacement not possible?

Thank you!

Solution 1

Try

Sub replace()
    Dim rng As Range

    For Each rng In Range("B2:AI40")
        If rng.Value < 5 And rng.Value > -5 Then
            rng = 0
        End If
    Next rng
End Sub

IsNumeric is a boolean function which either returns 0 or 1, that's why every cell was set to 0 before. If you expect non-numeric values in your cells, you might have to use this code:

Sub replace()
    Dim rng As Range

    For Each rng In Range("B2:AI40")
        If IsNumeric(rng) then
            If rng.Value < 5 And rng.Value > -5 Then
                rng = 0
            End If
        End if
    Next rng
End Sub

The whole task gets simpler (and deals also with your the other cases you mentioned) when you use integer division (\) by 5:

Sub replace()
    Dim rng As Range

    For Each rng In Range("B2:AI40")
        If IsNumeric(rng) then
            rng = rng.Value \ 5
        End if
    Next rng
End Sub

Solution 2

Please do not select this as an answer.

This post is only to show you a much much faster method than what you are actually using. Technically you are looping through every cell in B2:AI40. i.e your code is looping 1326 times (=34 Cols * 39 Rows)

Here is a much much faster method using only 17 loops

Logic:

1. < 10 And > 5 (In other words 6,7,8,9 to be replaced by 1)
2. < 5 And > -5 (In other words -4,-3,-2,-1,0,1,2,3,4 to be replaced by 0)
3. < -5 And > -10 (In other words -6,-7,-8,-9 to be replaced by -1)

Now instead of looping ever cell, we will be using .Replace to search the above numbers so technically we will only be looping 17 times as compared to 1326!

See this sample code

Option Explicit

Sub replace()
    Dim ws As Worksheet
    Dim rng As Range
    Dim i As Long

    Set ws = ThisWorkbook.Sheets("Sheet1")
    Set rng = ws.Range("B2:AI40")

    With rng
        For i = 6 To 9 '<~~ 4 loops
            .replace What:=i, Replacement:="1", LookAt:=xlWhole, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
        Next i

        For i = -4 To 4 '<~~ 9 loops ; 7 if when i = 1 or 0 it doesn't do anyting
            If i <> 1 And i <> 0 Then
                .replace What:=i, Replacement:="0", LookAt:=xlWhole, _
                SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                ReplaceFormat:=False
            End If
        Next i

        For i = -9 To -6 '<~~ 4 loops   
            .replace What:=i, Replacement:="-1", LookAt:=xlWhole, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
        Next i
    End With
End Sub