Writing to TXT depending of IF outcome

Question

I have a Excel file where some data are added it can be 1 row or it can be 1000 rows.

A VBA script matches which outcome that will be used depending of Column C & E:

Sub LoopRange()

Dim myFile As String, rng As Range, cellValue As Variant, i As Integer, j As Integer

Dim N As Long
N = Cells(1, 1).End(xlDown).Row

For i = 2 To N
    If Range("E" & i).Value = "South" And Range("C" & i).Value = "Yes" Then
        'Do this
    End If
Next i

For i = 2 To N
    If Range("E" & i).Value = "South" And Range("C" & i).Value = "No" Then
        'Do this
    End If
Next i

For i = 2 To N
    If Range("E" & i).Value = "West" And Range("C" & i).Value = "Yes" Then
        'Do this
    End If
Next i

For i = 2 To N
    If Range("E" & i).Value = "West" And Range("C" & i).Value = "No" Then
        'Do this
    End If
Next i

For i = 2 To N
    If Range("E" & i).Value = "North" And Range("C" & i).Value = "Yes" Then
        'Do this
    End If
Next i

For i = 2 To N
    If Range("E" & i).Value = "North" And Range("C" & i).Value = "No" Then
        'Do this
    End If
Next i

For i = 2 To N
    If Range("E" & i).Value = "East" And Range("C" & i).Value = "Yes" Then
        'Do this
    End If
Next i

For i = 2 To N
    If Range("E" & i).Value = "East" And Range("C" & i).Value = "No" Then
        'Do this
    End If
Next i

For i = 2 To N
    If Range("E" & i).Value = "NorthWest" And Range("C" & i).Value = "Yes" Then
        'Do this
    End If
Next i

For i = 2 To N
    If Range("E" & i).Value = "NorthWest" And Range("C" & i).Value = "No" Then
        'Do this
    End If
Next i

For i = 2 To N
    If Range("E" & i).Value = "NorthEast" And Range("C" & i).Value = "Yes" Then
        'Do this
    End If
Next i

For i = 2 To N
    If Range("E" & i).Value = "NorthEast" And Range("C" & i).Value = "No" Then
        'Do this
    End If
Next i

End Sub

I want to create different textfiles depending of which IF Statements that are true. The value to print to textfile is most static but the values in Column A & B which I want to use in the text.

For example: 1 and 9 row matches the IF Statement

If Range("E" & i).Value = "South" And Range("C" & i).Value = "Yes" Then...

I would then like to fetch the value in A2 and B2 to be used for one outcome in the textfile along with some other static code. And I would like to get the value in A9 and B2 in same textfile but another row.

I have found how to export data to TXT but I have not a clue how to choose what to retrieve depending of the IF Statements.

 myFile = Application.DefaultFilePath & "\South_Rename.txt"
     Set rng = Selection
        Open myFile For Output As #1
        For i = 1 To rng.Rows.Count
            For j = 1 To rng.Columns.Count
            cellValue = rng.Cells(i, j).Value
            If j = rng.Columns.Count Then
                Write #1, cellValue
            Else
                Write #1, cellValue,
            End If
        Next j
    Next i
 Close #1

Anyone with some suggestions?

Answer 1

How about something like below, keep in mind that Output will completely empty the Text file before writing to it, so I've included the Append method as an alternative to add to the text file without emptying its contents first, have a look at the comments in the code below:

Sub LoopRange()
    Dim myFile As String, rng As Range, cellValue As Variant, i As Integer, j As Integer
    Dim N As Long
    Dim ws As Worksheet: Set ws = Sheets("Sheet1")
    'declare and set your worksheet, amend as required
    N = ws.Cells(1, 1).End(xlDown).Row 'get the last row with data on Column A

    For i = 2 To N
        If ws.Range("E" & i).Value = "South" And ws.Range("C" & i).Value = "Yes" Then
            acellValue = ws.Range("A" & i)
            bcellValue = ws.Range("B" & i)
            myFile = Application.DefaultFilePath & "\South_Yes.txt" 'choose the text file to write to
            'Open myFile For Output As #1 'Output will re-write the file
            Open myFile For Append As #1 'Append will add the rows to the file
            Write #1, acellValue & ", " & bcellValue & vbNewLine
            Close #1

        ElseIf ws.Range("E" & i).Value = "South" And ws.Range("C" & i).Value = "No" Then
            acellValue = ws.Range("A" & i)
            bcellValue = ws.Range("B" & i)
            myFile = Application.DefaultFilePath & "\South_No.txt" 'choose the text file to write to
            Open myFile For Append As #1
            Write #1, acellValue & ", " & bcellValue & vbNewLine
            Close #1
        ElseIf ws.Range("E" & i).Value = "West" And ws.Range("C" & i).Value = "Yes" Then
            acellValue = ws.Range("A" & i)
            bcellValue = ws.Range("B" & i)
            myFile = Application.DefaultFilePath & "\West_Yes.txt" 'choose the text file to write to
            Open myFile For Append As #1
            Write #1, acellValue & ", " & bcellValue & vbNewLine
            Close #1
        ElseIf ws.Range("E" & i).Value = "West" And ws.Range("C" & i).Value = "No" Then
            acellValue = ws.Range("A" & i)
            bcellValue = ws.Range("B" & i)
            myFile = Application.DefaultFilePath & "\West_No.txt" 'choose the text file to write to
            Open myFile For Append As #1
            Write #1, acellValue & ", " & bcellValue & vbNewLine
            Close #1
        ElseIf ws.Range("E" & i).Value = "North" And ws.Range("C" & i).Value = "Yes" Then
            acellValue = ws.Range("A" & i)
            bcellValue = ws.Range("B" & i)
            myFile = Application.DefaultFilePath & "\North_Yes.txt" 'choose the text file to write to
            Open myFile For Append As #1
            Write #1, acellValue & ", " & bcellValue & vbNewLine
            Close #1
        ElseIf ws.Range("E" & i).Value = "North" And ws.Range("C" & i).Value = "No" Then
            acellValue = ws.Range("A" & i)
            bcellValue = ws.Range("B" & i)
            myFile = Application.DefaultFilePath & "\North_No.txt" 'choose the text file to write to
            Open myFile For Append As #1
            Write #1, acellValue & ", " & bcellValue & vbNewLine
            Close #1
        ElseIf ws.Range("E" & i).Value = "East" And ws.Range("C" & i).Value = "Yes" Then
            acellValue = ws.Range("A" & i)
            bcellValue = ws.Range("B" & i)
            myFile = Application.DefaultFilePath & "\East_Yes.txt" 'choose the text file to write to
            Open myFile For Append As #1
            Write #1, acellValue & ", " & bcellValue & vbNewLine
            Close #1
        ElseIf ws.Range("E" & i).Value = "East" And ws.Range("C" & i).Value = "No" Then
            acellValue = ws.Range("A" & i)
            bcellValue = ws.Range("B" & i)
            myFile = Application.DefaultFilePath & "\East_No.txt" 'choose the text file to write to
            Open myFile For Append As #1
            Write #1, acellValue & ", " & bcellValue & vbNewLine
            Close #1
        ElseIf ws.Range("E" & i).Value = "NorthWest" And ws.Range("C" & i).Value = "Yes" Then
            acellValue = ws.Range("A" & i)
            bcellValue = ws.Range("B" & i)
            myFile = Application.DefaultFilePath & "\NorthWest_Yes.txt" 'choose the text file to write to
            Open myFile For Append As #1
            Write #1, acellValue & ", " & bcellValue & vbNewLine
            Close #1
        ElseIf ws.Range("E" & i).Value = "NorthWest" And ws.Range("C" & i).Value = "No" Then
            acellValue = ws.Range("A" & i)
            bcellValue = ws.Range("B" & i)
            myFile = Application.DefaultFilePath & "\NorthWest_No.txt" 'choose the text file to write to
            Open myFile For Append As #1
            Write #1, acellValue & ", " & bcellValue & vbNewLine
            Close #1
        ElseIf ws.Range("E" & i).Value = "NorthEast" And ws.Range("C" & i).Value = "Yes" Then
            acellValue = ws.Range("A" & i)
            bcellValue = ws.Range("B" & i)
            myFile = Application.DefaultFilePath & "\NorthEast_Yes.txt" 'choose the text file to write to
            Open myFile For Append As #1
            Write #1, acellValue & ", " & bcellValue & vbNewLine
            Close #1
        ElseIf ws.Range("E" & i).Value = "NorthEast" And ws.Range("C" & i).Value = "No" Then
            acellValue = ws.Range("A" & i)
            bcellValue = ws.Range("B" & i)
            myFile = Application.DefaultFilePath & "\NorthEast_No.txt" 'choose the text file to write to
            Open myFile For Append As #1
            Write #1, acellValue & ", " & bcellValue & vbNewLine
            Close #1
        End If
    Next i
End Sub