Excel VBA Compile Error Else without If

Question

I try to run a code when column "L" and column "V" stated "Completed" on both. Then column "W" will show "Completed" else it will show "incompleted" but it show compile error "Else withou if".

Below is my code

Sub OverallStatus()

Dim x As Long
Dim lastrow As Long

With Sheet1
    If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
         lastrow = .Cells.Find(What:="*", _
                     After:=.Range("A1"), _
                     Lookat:=xlPart, _
                     LookIn:=xlFormulas, _
                     SearchOrder:=xlByRows, _
                     SearchDirection:=xlPrevious, _
                     MatchCase:=False).Row
    Else
        lastrow = 1
    End If
    For x = 2 To lastrow
        If .Range("L" & x) = "Completed" And .Range("V" & x) = "Completed" Then
        .Range("W" & x) = "Completed"
        Else: .Range("W" & x) = "Incomplete"
        End If
    Next
End With
End Sub

In your case the "Then" must be on the same line as the "If" but it should be the last thing on the line and the part after the "Then" should be on a separate line. If you put anything after the "Then" on the same line as the "If" then the "If" is a single line statement and then "Else" and "End If" must also be on that same line. — Jerry Jeremiah
– Jerry Jeremiah, Commented Feb 21, 2014 at 3:08
Please do not post images of your code. It makes the code difficult to read, prevents copying/pasting to test or solve problems, and it's rather rude to users who have limited bandwidth. Copy your code and/or any relevant error messages and paste it as text into your question. Images should only be used when nothing else will work to explain the issue you're having. Please edit to include the actual code. (A capture of your entire screen is also in no way relevant to the question asked; you could at least be courteous enough to crop it to the minimal size needed.) Thanks. — Ken White
– Ken White, Commented Feb 21, 2014 at 3:11
End if should be placed after .range("W" & x) = "Incomplete" ...Not after next — Sathish Kothandam
– Sathish Kothandam, Commented Feb 21, 2014 at 4:35

PatricK · Accepted Answer · 2014-02-21 04:52:57Z

1

Things to change on your code:

New line character after Then, or move this back up at end of the If
Insert End If before the Next
Replace End If above the End Sub with End With

answered Feb 21, 2014 at 4:52

PatricK

6,4231 gold badge23 silver badges25 bronze badges

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

2 Comments

user3006276 Over a year ago

I've already updated my code accordingly, but its still showing Else without if error.

user3006276 Over a year ago

Thank you so much, i just updated the final amendment. Finally it works. Thank you so much for your help.

Collectives™ on Stack Overflow

Excel VBA Compile Error Else without If

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related