using "Not equal" operator in Excel VBA

Question

I'm currently working on a small macro that inserts a row if one of the following columns are a different number. I have the below peice of code but cannot understan why this isn't working.

Could anyone point out my mistake?

Sub Button1_Click()

Dim rowCount As Integer
rowCount = 10

If cell(rowCount, 2) <> cell((rowCount + 1), 2) Then
    Rows(rowCount).Insert
    rowCount = rowCount + 2
ElseIf cell(rowCount, 2) = cell((rowCount + 1), 2) Then
    rowCount = rowCount + 1

End Sub

as an aside `rowCount` will not increment between calls of this sub. — Warcupine, Feb 03 '21 at 13:37
Where is the `EndIf` and why not just `Else`, but have `ElseIf`? — JAlex, Feb 03 '21 at 13:38
[edit] the question and make it valid syntax and also explain _how_ it is not working. — JAlex, Feb 03 '21 at 13:39

score 3 · Accepted Answer · edited Feb 03 '21 at 14:47

3

cell is neither defined nor Set... use Cells.
It's best to be explicit: add the .Value to indicate you are comparing values.
Use a simple Else instead of ElseIf... and include the End If.

If Cells(rowCount, 2).Value <> Cells((rowCount + 1), 2).Value Then
    Rows(rowCount).Insert
    rowCount = rowCount + 2
Else
    rowCount = rowCount + 1
End If

P.S. Do not use Integer for tracking rows, but use Long instead always. See this thread for the full detail.

P.P.S. Always use Option Explicit, which would flag the undeclared variable cell.

edited Feb 03 '21 at 14:47

BigBen

46,229
7
24
40

answered Feb 03 '21 at 13:42

JAlex

1,486
8
19

Agree with comments but it does not implies a down vote as attested by the OP satisfaction. JAlex spent time helping and deserves a thank you. – Tarik Feb 03 '21 at 14:02

using "Not equal" operator in Excel VBA

1 Answers1