Simple loop in VBA not working and how to use If statements

Question

This is my first time coding anything in VBA - I am trying to write a short macro which reads a file and separates bits and pieces into either column west or column east.

All of it could be wrong or it could just be something simple, but at the moment it won't even recognize my do loop. Any help is much appreciated.

    Private Sub seperateTextFile()
    Dim file As String
    Dim text As String
    Dim textLine As String

    Dim west As Boolean
    west = True

    file = ".alltxt"

    Open file For Input As #1

    Do Until EOF(1)
        Line Input #1, textLine
        text = textLine
        If InStr(text, "HMW") <> 0 Then
            west = True
        If InStr(text, "other") <> 0 Then
            west = False
        If west = True Then
            Sheets("Sheet2").Range("West").End(xlUp) = text
        If west = False Then
            Sheets("Sheet2").Range("East").End(xlUp) = text
    Loop

    Close #1

    End Sub

Unless If {test} Then {Action} is all on one line it requires an End If — Tim Williams
– Tim Williams, Commented Jun 25, 2016 at 22:38

braX · Accepted Answer · 2020-02-02 07:53:44Z

1

I think you are missing your elesif where you have ifs. And you need an End if

Dim file As String
Dim text As String
Dim textLine As String

Dim west As Boolean
west = True

file = "c:\temp\a.alltxt"

Open file For Input As #1

Do Until EOF(1)
    Line Input #1, textLine
    text = textLine
    If InStr(text, "HMW") <> 0 Then
        west = True
    ElseIf InStr(text, "other") <> 0 Then
        west = False
    ElseIf west = True Then
        'Sheets("Sheet2").Range("West").End(xlUp) = text
    ElseIf west = False Then
        'Sheets("Sheet2").Range("East").End(xlUp) = text
    End If
Loop

Close #1

And your file = ".alltxt" for me needed to have an actual path and name such as file = "c:\temp\a.alltxt"

edited Feb 2, 2020 at 7:53

braX

11.9k5 gold badges24 silver badges38 bronze badges

answered Jun 25, 2016 at 22:47

MatthewD

6,8215 gold badges24 silver badges44 bronze badges

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

1 Comment

user3431504 Over a year ago

They are not exclusive so I needed to move them all onto individual lines as the above comment found. The file path I had removed on purpose but thank you for pointing this out.

Collectives™ on Stack Overflow

Simple loop in VBA not working and how to use If statements

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related