CODEWITHSUNDEEP
vbaexcel
Pablo
Pablo

Reputation: 534

Scan through a range finding values and leaving blank (or zero) when condition is met

I have the following code:

Sub DELETE2()

Dim dat As Variant
Dim rng As Range
Dim i As Long

Set rng = Range("C12:K89")
dat = rng
For i = LBound(dat, 1) To UBound(dat, 1)
    If dat(i, 1) = "3" Or dat(i, 1) = "2" Then
        dat(i, 1) = ""
    End If
Next
rng = dat

End Sub

My goal was to find in that range all cells with value 3 or 1 and leaving blank (or fill with a 0) when the condition is met, but that is not working, no error message or output.

Upvotes: 0

Views: 42

Answers (2)

Siva
Siva

Reputation: 1149

I think Excel Replace All will serve this simply and you don't need a macro to do this.

If you are still looking for macro code try below one. I tried the below code and it worked fine for me with my sample data.

Sub DELETE2()

Dim rng As Range
Dim cellval  As Variant


Set rng = Range("C12:K89")

For Each cellval In rng.Cells

        If (cellval .Value = "3" Or cellval .Value = "2") Then cellval .Value = ""


Next cellval 

End Sub

Upvotes: 1

arcadeprecinct
arcadeprecinct

Reputation: 3777

Siva already gave an answer but here is a way to "fix" your code:

To loop through the whole array, use a second parameter for the column

Dim i As Long
Dim j As Long
'...
For i = LBound(dat, 1) To UBound(dat, 1)
    For j = LBound(dat, 2) To UBound(dat, 2)
        If dat(i, j) = "3" Or dat(i, j) = "2" Then
            dat(i, j) = ""
        End If
    Next j
Next i

You can use a For Each loop on arrays but you can only access the elements not replace them in the array because you only get the value, not the reference.

If you don't want to do it with an array, it can be with a For Each loop:

Dim c As Range
For Each c In rng
    If c.Value = "3" Or c.Value = "2" Then
        c.Value = ""
    End If
Next c

The Replace method probably would be the fastest though

Upvotes: 1

Related Questions