Else If Syntax Error

[johngio]

Hi all,

I'm trying to use the following piece of code and keep getting a Syntax error for the Else If command.

Sub Macro1()

Dim iRow As Integer

For iRow = 16 To 50
If Cells(iRow - 1, 7) - Cells(iRow, 7) = Cells(iRow - 1, 7) Then

Cells(iRow, 7) = "0"

Else If

Cells(iRow - 1, 7) - Cells(iRow, 7) = "0" Then

Cells(iRow, 7) = "0"

Else

Cells(iRow, 7).Value = Cells(iRow - 1, 7) + Range("Q14")

End If

Next iRow

This is a valid command as far as I can tell. Any ideas why this might be happening?

Thanks

John

 

[Scott Huish]

First, I think you're whole code could be reduced to this:

Sub test()
Dim iRow As Integer
For iRow = 16 To 50
    If Cells(iRow, 7) = "" Or Cells(iRow - 1, 7) = Cells(iRow, 7) Then
        Cells(iRow, 7) = 0
    Else
        Cells(iRow, 7) = Cells(iRow - 1, 7) + Range("Q14")
    End If
Next iRow
End Sub

Also, don't put numbers in quotes unless you actually want them treated as text.

but as far as ElseIf, it's one word.

 

[Norie]

John

I don't think the syntax is quite right.

This compiles.

Dim iRow As Long

For iRow = 16 To 50

    If Cells(iRow - 1, 7) - Cells(iRow, 7) = Cells(iRow - 1, 7) Then
        Cells(iRow, 7) = "0"
    
    ElseIf Cells(iRow - 1, 7) - Cells(iRow, 7) = "0" Then
    
        Cells(iRow, 7) = "0"
    
        Cells(iRow, 7).Value = Cells(iRow - 1, 7) + Range("Q14")
    
    End If

Next iRow

 

[Scott Huish]

You don't need the ElseIf at all, but Norie you're missing an Else

 

[johngio]

Just realised I made a mistake.

I meant:

Sub Macro1()

Dim iRow As Integer

For iRow = 16 To 50
If Cells(iRow - 1, 7) - Cells(iRow, 7) = Cells(iRow - 1, 7) Then

Cells(iRow, 7) = "0"

Else If

Cells(iRow - 1, 7) - Cells(iRow, 7) = "0" Then

Cells(iRow, 7) = Cells(iRow-1,7)

Else

Cells(iRow, 7).Value = Cells(iRow - 1, 7) + Range("Q14")

End If

Next iRow

Thanks; I should be able to work off of that info

 

[Scott Huish]

How's that different from what you posted before? Did you try my code?

 

[jindon]

Hi

Else If should be ElseIf and the condition should be on the same line
i.e

Sub Macro1()
Dim iRow As Integer
For iRow = 16 To 15
   If Cells(iRow-1,7) / Cells(iRow,7) = 2 Then 
      Cells(iRow,7) = 0
   ElseIf Cells(iRow,7) = Cells(iRow,7) Then
      Cells(iRow,7) = 0
   Else
      Cells(iRow,7)=Cells(iRow-1,7) + Range("q14")
   End If
Next
End Sub

 

[Scott Huish]

jindon:

How did you get this:
Cells(iRow-1,7) / Cells(iRow,7) = 2

My logic on it was this:
If Cells(iRow - 1, 7) - Cells(iRow, 7) = Cells(iRow - 1, 7) Then

Cells(iRow, 7) = "0"

The only thing that you can subtract from itself and get the original value is 0, so it is already 0 or blank, so blank is all you have to check for, otherwise check for equality:

from this part:

ElseIf Cells(iRow - 1, 7) - Cells(iRow, 7) = "0" Then

Cells(iRow, 7) = "0"

Since the same thing is being done in both instances, an Or will accomplish both, else do the last part:

Cells(iRow, 7).Value = Cells(iRow - 1, 7) + Range("Q14")

 

[jindon]

OOps!
I think I misread the formula...

 

[johngio]

I have edited this formula to be:

Sub Stacking()

Dim iRow As Integer

For iRow = 16 To 50

If IsEmpty(Cells(iRow, 8)) Then

If Cells(iRow - 2, 8) - Cells(iRow - 1, 8) = "0" Then
Cells(iRow, 8) = Cells(iRow - 1, 8)


ElseIf Cells(iRow - 1, 8) - Cells(iRow - 2, 8) = Cells(iRow - 1, 8) Then
Cells(iRow, 8) = Cells(iRow - 1, 8)

ElseIf Cells(iRow - 2, 8) - Cells(iRow - 1, 8) = Range("Q14") Then
Cells(iRow, 8).Value = Cells(iRow - 1, 8) + Range("Q14")

Else: Cells(iRow, 8).Value = Cells(iRow - 1, 8) + Range("Q14")


End If
End If
Next iRow


End Sub

Works well. Only problem is now I want to try and do this for 3 columns simultaneously (7,8 and 9)

Any idea how I would manipulate this formula to do this?

Thanks

John