Excel VBA "End If without block If" - VBA error

Question

I can not figure out why VBA is yelling at me on this one. Very simple code, only one if statement is involved, and it clearly has a "Starting" If and a matching End If. I am a VB.NET developer, so it may be a difference in the syntax that I am missing.

 Sub splitter()
   Dim line() As String
   Dim rng As Range
   Dim row As Range
   Dim cell As Range
      For Each row In rng.Rows
        If row.Value <> "" Then
            line = Split(Range(row, 1), ",")
            Select Case line(0)
               Case "Desktop"
                   Range(row, 8).Value = "Desktop"
               Case "Laptop"
                   Range(row, 8).Value = "Laptop"
               Case "Server"
                   Range(row, 8).Value = "Server"
               Case Else
                   Range(row, 8).Value = "N/A"
        End If
       Next
    End Sub

Specifically, the end result is that it will populate a "Child" drop down list (Range(row, 8)) based on the selection made from of the "Parent" drop down list (Range(row, 1)). Because the question will arise, the reason I am doing this using VBA is because I can utilize the Split() function with the way the items in the Parent drop down list are made eg. "Desktop, Dell, 745". Also, I am a better programmer than an excel developer.

Jon Crowell · Accepted Answer · 2012-07-12 20:28:56Z

8

Your select statement needs an end:

Select Case line(0)
   Case "Desktop"
       Range(row, 8).Value = "Desktop"
   Case "Laptop"
       Range(row, 8).Value = "Laptop"
   Case "Server"
       Range(row, 8).Value = "Server"
   Case Else
       Range(row, 8).Value = "N/A"
End Select

answered Jul 12, 2012 at 20:28

Jon Crowell

22.4k15 gold badges94 silver badges113 bronze badges

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

2 Comments

Josh McKearin Over a year ago

Thank you sir! Will vote up as answer. However, with that change, I am getting Runtime Error '91': Object variable or With block variable not set. Do all of my Dim'd variables have to be set to something (or "Nothing")?

Josh McKearin Over a year ago

Never mind I see the problem. I need to set my rng Range. Thanks!

Collectives™ on Stack Overflow

Excel VBA "End If without block If" - VBA error

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