Else If Syntax Error

johngio

Board Regular
Joined
Jan 28, 2005
Messages
174
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
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
First, I think you're whole code could be reduced to this:

Code:
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

Well-known Member
Joined
Apr 28, 2004
Messages
75,946
Office Version
  1. 365
Platform
  1. Windows
John

I don't think the syntax is quite right.

This compiles.
Code:
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

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
You don't need the ElseIf at all, but Norie you're missing an Else
 

johngio

Board Regular
Joined
Jan 28, 2005
Messages
174

ADVERTISEMENT

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

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
How's that different from what you posted before? Did you try my code?
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995

ADVERTISEMENT

Hi

Else If should be ElseIf and the condition should be on the same line
i.e
Code:
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

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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")
 

johngio

Board Regular
Joined
Jan 28, 2005
Messages
174
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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,674
Messages
5,549,358
Members
410,910
Latest member
DessertDiva
Top