locked
How to replace the content of all cells with positive numers? RRS feed

  • Question

  • Goal: replace  content of all cells with a positive number (number > 0) with the number "1". There are also many empty cells and some with "0".

    Range: "G8:AQ81"

    How to do this in VBA?

    Friday, November 16, 2012 1:00 PM

Answers

  • Try this:

    Sub ReplacePositive()
        Dim rng As Range
        Application.ScreenUpdating = False
        For Each rng In Range("G8:AQ81")
            If IsNumeric(rng.Value) Then
                If rng.Value > 0 Then
                    rng.Value = 1
                End If
            End If
        Next rng
        Application.ScreenUpdating = True
    End Sub


    Regards, Hans Vogelaar

    Friday, November 16, 2012 1:16 PM

All replies

  • Try this:

    Sub ReplacePositive()
        Dim rng As Range
        Application.ScreenUpdating = False
        For Each rng In Range("G8:AQ81")
            If IsNumeric(rng.Value) Then
                If rng.Value > 0 Then
                    rng.Value = 1
                End If
            End If
        Next rng
        Application.ScreenUpdating = True
    End Sub


    Regards, Hans Vogelaar

    Friday, November 16, 2012 1:16 PM
  • Many thanks! Works perfectly!
    Friday, November 16, 2012 1:38 PM