VBA separate uppercase words from lowercase words.

[mdalala]

Hi everyone. Basically, I have a txt file with different words and each one has it's own row. I have to copy them using VBA from txt to Excel file and then separate the UPPERCASE and LOWERCASE words. Words that start with the UPPERCASE should be in column A, but words that start with LOWERCASE should be in column B.
I have this code so far which copies the words from txt file to excel:

Public Sub aaa()


    Dim i As Long, j As Long
    Dim myStr As String
    Dim Words() As String


    'CHECK THE FILE EXISTS, IF NOT QUIT
    If Dir("C:\Users\Robert\Desktop\ekselis\forexcel.txt") = "" Then
        MsgBox ("File not Found")
        Exit Function 'ADDED QUIT MESSAGE
    End If


    'FILE TO OPEN
    Open "C:\Users\Robert\Desktop\ekselis\forexcel.txt" For Input As #1


    'LOOP COUNTER
    i = 0


    'LOOP THROUGH FILE
    Do Until EOF(1)


        'READ A LINE
        Line Input #1, myStr


        'SEPERATE THE LINE BY WORDS
        Words = Split(myStr, " ")


        'LOOP THROUGH WORDS ARRAY
        For j = LBound(Words) To UBound(Words)


            'IF THE WORD IS NOT A ""
            If Words(j) <> "" Then


                'THEN PLACE THE WORD IN THE CELL
                ActiveCell.Offset(i, j) = Words(j)


            End If


        Next


        i = i + 1
    Loop
    
    


    Close #1


End Function

I`m not really sure how to organise the IF statements in a loop to complete my task... Thanks!

[Kenneth Hobs]

cross-posted https://www.excelforum.com/excel-pro...ase-words.html

[Paul_Hossler]

Welcome

1. Please check the FAQs about multi-posting

2. Please use the [#] icon to add CODE tags and paste the macro between them

Not tested, but maybe something like this

Option Explicit

Public Sub aaa()
    Dim iUC As Long, iLC As Long, i As Long
    Dim myStr As String
    Dim Words() As String
     
    If Dir("C:\Users\Robert\Desktop\ekselis\forexcel.txt") = "" Then
        MsgBox ("File not Found")
        Exit Sub
    End If
     
    iUC = 1
    iLC = 1
     
    Open "C:\Users\Robert\Desktop\ekselis\forexcel.txt" For Input As #1
     
    Do While Not EOF(1)
        Line Input #1, myStr
        If Len(Trim(myStr)) > 0 Then
            Words = Split(myStr, " ")
            For i = LBound(Words) To UBound(Words)
                Select Case Left(Words(i), 1)
                    Case "A" To "Z"
                        ActiveSheet.Cells(iUC, 1).Value = Words(i)
                        iUC = iUC + 1
                    Case "a" To "z"
                        ActiveSheet.Cells(iLC, 2).Value = Words(i)
                        iLC = iLC + 1
                End Select
            Next
        End If
    Loop
    
    Close #1
End Sub