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. :)

Answer 1

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!

Answer 2

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

Answer 3

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