Excel VBA Else without if

Question

I want to use a if-function to distingiush between two sceneraios.

For Each Cell In Tabelle3.Range("A" & lastrow2)

Option A: If Cell <> "" Then run code
Option B: If Cell = "" Then skip this empty cell and go on with the next one

Here the whole code:

Sub IfFunction()

Dim lastrow2 As Long
lastrow2 = Tabelle3.Range("A" & Rows.Count).End(xlUp).Row
Set myrange2 = Tabelle8.UsedRange


    For i = 2 To lastrow2

    For Each Cell In Tabelle3.Range("A" & lastrow2)

    If Cell = "" Then i = i + 1

    Else: i = i



        Tabelle3.Cells(7 + i, 19) = Application.WorksheetFunction.VLookup(Tabelle3.Cells(7 + i, 1), myrange2, 3, False)



        Tabelle3.Cells(7 + i, 20) = Application.WorksheetFunction.VLookup(Tabelle3.Cells(7 + i, 1), myrange2, 4, False)



        Tabelle3.Cells(7 + i, 21) = Application.WorksheetFunction.VLookup(Tabelle3.Cells(7 + i, 1), myrange2, 5, False)

        Next i

    End If


End Sub

When I try to run this code, it does not execute because an error occurs that there is a 'ELSE without IF'-Function.

Does anyone know how I can use an IF-function here or what to use instead? Thanks. :)

Unrelated to your IF statement problem, you are missing and 'next cells' — Jarom
– Jarom, Commented Mar 21, 2018 at 16:06
No, in addition to 'Next i' You have two loops in your code. VBA needs to know when to go back to the beginning of each loop. The 'Next' statement tells VBA to go to the beginning of the loop. Your 'next i' statement tells VBA to go to the beginning of the inner loop. You need a 'next cells' statement to tell VBA to go to the beginning of the 'For each' loop that goes through the cells. — Jarom
– Jarom, Commented Mar 21, 2018 at 16:17

Community · Accepted Answer · 2020-06-20 09:12:55Z

5

if you continue writing after Then this means the If statement consists of one line only:

If Cell = "" Then i = i + 1 'End If automatically here

Then the Else has to be in that line too:

If Cell = "" Then i = i + 1 Else i = i 'End If automatically here

If you want to use a multi line If statement

If Cell = "" Then 
    i = i + 1
Else
    i = i
End If

But …

because i = i doesn't do anything you can just write

If Cell = "" Then i = i + 1

and omit the Else part completely because it does nothing at all.

And anther but …

because you are using a For i the Next i increments i automatically and you don't need to increment it yourself. There is no i = i + 1 needed

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Mar 21, 2018 at 16:00

Pᴇʜ

57.9k10 gold badges55 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

HPM Over a year ago

@PEH: About your 2. But: Doesn't this imply that If the cell is empty it will run the code for this cell instead of skipping this cell and going on with the next cell?

Pᴇʜ Over a year ago

@HPM No, you can just use If Cell <> "" Then … End If and put your code in between. So your code runs when the cell is NOT empty. Or you write If Not Cell = "" Then which also implies that the cell is not empty.

DisplayName · Accepted Answer · 2018-03-21 16:06:46Z

your code has to For but one Next only, which would result in a syntax error

furthermore the Next i is intertwined with a If-Then-Else block code which would also result in a syntax error

finally I guess you're iterating twice along Tabelle3 column A cells from row 2 to last not empty one, while you only need it once

Summing all that up, I'd say you can use this code:

Option Explicit

Sub IfFunction()
    Dim myrange2 As Range, cell As Range
    Set myrange2 = Tabelle8.UsedRange

    With Tabelle3
        For Each cell In .Range("A2:A" & .Cells(.Rows.count, 1).End(xlUp)).SpecialCells(xlCellTypeConstants)
            cell.Offset(7, 18) = Application.WorksheetFunction.VLookup(cell.Offset(7), myrange2, 3, False)

            cell.Offset(7, 19) = Application.WorksheetFunction.VLookup(cell.Offset(7), myrange2, 4, False)

            cell.Offset(7, 20) = Application.WorksheetFunction.VLookup(cell.Offset(7), myrange2, 5, False)
        Next
    End With
End Sub

HPM · Accepted Answer · 2018-03-22 15:59:46Z

0

Okay that was actually way to simple :D I was running through the same column twice by

For Each Cell In Tabelle3.Range("A" & lastrow2)

If Cell = "" Then i = i + 1

Else: i = i

and

For i = 2 To lastrow2

Instead I can simply use:

For i = 2 To lastrow2

If Tabelle3.Cells(7 + i, 1) <> "" Then



Tabelle3.Cells(7 + i, 19) = Application.WorksheetFunction.VLookup(Tabelle3.Cells(7 + i, 1), myrange2, 3, False)



Tabelle3.Cells(7 + i, 20) = Application.WorksheetFunction.VLookup(Tabelle3.Cells(7 + i, 1), myrange2, 4, False)



Tabelle3.Cells(7 + i, 21) = Application.WorksheetFunction.VLookup(Tabelle3.Cells(7 + i, 1), myrange2, 5, False)

End if
Next i

Thanks alot for your help & contribution!

edited Mar 22, 2018 at 15:59

answered Mar 21, 2018 at 16:39

HPM

1131 silver badge8 bronze badges

2 Comments

DisplayName Over a year ago

that seems exactly what I noticed and amended in my answer! But your solution would skip two lines instead of the empty one only. try my code

Pᴇʜ Over a year ago

you should really not use i = i + 1 within a For i loop. you can just use If Cell <> "" Then … End If and put your code in between. So your code runs when the cell is NOT empty. Or you write If Not Cell = "" Then which also implies that the cell is not empty.

Collectives™ on Stack Overflow

Excel VBA Else without if

3 Answers 3

But …

And anther but …

2 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

But …

And anther but …

2 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related