Trying to understand how to write an if then elseif statement using certain parameters

Question

Yes, I'm a VBA incompetent. Just learning, and I need someone to look at this and point me in the right direction. I want to write an if statement for a range of cells. I want the macro to look at each cell, if the cell = 0, clear cell, if cell <> 0, then do nothing, look at next cell.

Here is my pitiful attempt.

Sub MACRO6()
    Dim VM As String
    VM = Range("D29:E43")
    Sheets("10").Select
    For Each VM In Range
        If cell = 0 Then ClearContents
        ElseIf cell <> 0 Then
        End If
    Next
End Sub

Answer 1

Your ElseIf is unnecessary. It will do nothing by default. Just remove that line. Because your If ... Then is on one line, you also don't need End If and should remove it.

You want something like the below (tweaked from this example):

Sub ClearZero()
    For Each c In Worksheets("10").Range("D29:E43").Cells
        If c.Value = 0 Then c.Value = ""
    Next
End Sub

Answer 2

Something like this as:

1. You have two ranges to work with, the range defined by D29:E43 (rng1 below), and then a loop through each cell in this range (rng2)
2. Note that rng1 has been set directly with reference to Sheet 10 to ensure it runs on the right sheet (and that sheet doesn't need to be selected)
3. You need to specify a range to ClearContents on

code

Sub MACRO6()
Dim rng1 As Range
Dim rng2 As Range
Set rng1 = Sheets("10").Range("D29:E43")
 For Each rng2 In rng1
    If rng2.Value = 0 Then rng2.ClearContents
Next
End Sub