VBA Issue with If/And/Then/Else statement using ActiveCell..Offset(-1,0)=

user_15926622 0
Viewed 171

I would like to select a cell in column C and check if the two cells above it are equal to 0. If they are equal to 0, I would like the ActiveCell to equal 1 Else I would like the ActiveCell to equal 0. I would then like to select the cell that is down 3 from the initial cell and repeat the process. I would like to do this 773 times. The issue I'm having is with the IF/AND section, it is always selecting 0 even when it should select a 1. Any idea what I did wrong. Working in an excel file that was converted from a CSV.

Range("C4").Select

For i = 1 To 773

If ActiveCell.Offset(-1, 0).Value = “0” And ActiveCell.Offset(-2, 0).Value = “0” Then 
    ActiveCell = "1" Else
        ActiveCell = "0"

ActiveCell.Offset(3, 0).Select

Next i
vba excel
3 Answers

It is best to avoid Activate and Select. Also, you can step the increment without having to add to the selection.

Sub test()
 
    Dim sht As Workbook
    Set sht = ActiveWorkbook 'or actual sheet name
    
    For i = 4 To 773 Step 3
    
    If sht.Cells(i - 1, 4).Value = 0 And sht.Cells(i - 2, 4).Value = 0 Then
        sht.Cells(i, 4) = 1
        Else
            sht.Cells(i, 4) = 0
    End If

    Next i
    
End Sub
user_64666940

Testing a Range

  • The unexpected result (zeros) is due to the use of "0" which cannot be found.
Option Explicit

' Not recommended. Note how slow it is compared to the other solutions.
' The trick is in avoiding using "Select" and any 'flavor' of "Active".
' Runtime: 2100ms (over 2 seconds)
Sub testQuickFix()
    
    Range("C4").Select
    
    Dim i As Long
    
    For i = 1 To 773
    
        ' This has to be one line: note the line separators ('_').
        If ActiveCell.Offset(-1, 0).Value = 0 _
            And ActiveCell.Offset(-2, 0).Value = 0 Then _
            ActiveCell = 1 Else _
            ActiveCell = 0
    
        ActiveCell.Offset(3, 0).Select
    
    Next i

End Sub

' Highly recommended, but maybe too advanced (You should learn about arrays).
' Note that this is only useful if the data are values, not formulas, because
' the whole range is overwritten.
' Runtime: 5ms
Sub testArray()
    
    Dim rg As Range: Set rg = Range("C2:C2320")
    Dim Data As Variant: Data = rg.Value
    
    Dim i As Long
    
    For i = 1 To UBound(Data, 1) Step 3
        If Data(i, 1) = 0 And Data(i + 1, 1) = 0 Then
            Data(i + 2, 1) = 1
        Else
            Data(i + 2, 1) = 0
        End If
    Next i

    rg.Value = Data

End Sub


' Recommended.
' Runtime: 80ms
Sub testIfThenElseEndIf()
    
    Dim rg As Range: Set rg = Range("C2:C4")
    
    Dim i As Long
    
    For i = 1 To 773
        
        ' This is considered more readable and is mostly used on SO.
        If rg(1).Value = 0 And rg(2).Value = 0 Then
            rg(3).Value = 1
        Else
            rg(3).Value = 0
        End If
        
        ' It is short for:
'        If rg.Cells(1).Value = 0 And rg.Cells(2).Value = 0 Then
'            rg.Cells(3).Value = 1
'        Else
'            rg.Cells(3).Value = 0
'        End If
        ' which I actually prefer.
        
        Set rg = rg.Offset(3)
    
    Next i

End Sub

' Not recommended.
' Runtime: 80ms
Sub testIfThenElse()
   
    Dim rg As Range: Set rg = Range("C2:C4")
    
    Dim i As Long
    
    For i = 1 To 773
        
        ' This is valid, but rarely seen on SO.
        
        ' Note that this is one line.
        'If rg(1).Value = 0 And rg(2).Value = 0 Then rg(3).Value = 1 Else rg(3).Value = 0
        
        ' Note that this is also one line, but uses a line separator.
        If rg(1).Value = 0 And rg(2).Value = 0 Then rg(3).Value = 1 _
            Else rg(3).Value = 0
        
        Set rg = rg.Offset(3)
    Next i

End Sub

' (A little less) recommended.
' Runtime: 80ms
Sub testIIF()
    
    Dim rg As Range: Set rg = Range("C2:C4")
    
    Dim i As Long
    
    For i = 1 To 773
        
        ' This is another way.
        rg(3).Value = IIf(rg(1).Value = 0 And rg(2).Value = 0, 1, 0)
        
        Set rg = rg.Offset(3)
    
    Next i

End Sub
user_98140690

I believe this would work for your instance.

Public Sub ReadingCells()

With Sheet1
Range("C4").Select
For i = 1 To 773

If ActiveCell.Offset(-1, 0).Value + ActiveCell.Offset(-2, 0).Value = "0" Then
  ActiveCell.Value = "1"
  Else
  ActiveCell.Value = "0" 
End If

ActiveCell.Offset(3, 0).Select

Next i

End With

End Sub
user_130396500
Related