# Else If Syntax Error

#### johngio

##### Board Regular
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

### Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

#### Scott Huish

##### MrExcel MVP
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
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
You don't need the ElseIf at all, but Norie you're missing an Else

#### johngio

##### Board Regular

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
How's that different from what you posted before? Did you try my code?

#### jindon

##### MrExcel MVP

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

##### MrExcel MVP
OOps!
I think I misread the formula...

#### johngio

##### Board Regular
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

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Threads
1,168,128
Messages
5,857,529
Members
431,883
Latest member
Hien

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back