Help with "Subscript out of range"

[thilooter]

Hi guys,

New to this. Can you please help me find out why the script show this error? Also is there a way to load the txt file from a google drive location?

The script basically search a column for a set of words in the txt file and highlight any row containing the words. If anyone have a better script for this let me know.

Sub lazyLala()
    'Variables
    Dim wordlist() As String, size As Integer
    Dim textFile As String
    Dim str As String
   
    'Initializer
    textFile = ActiveWorkbook.Path & "/SearchWords.txt"
   
    'Get Length of List
    Open textFile For Input As #1
    str = Input(LOF(1), 1)
    size = Len(str) - Len(Replace(str, vbLf, "")) + 1
    Close #1

    'Get List
    ReDim wordlist(size)
    Dim x As Integer
    x = 1
   
    Open textFile For Input As #1
    Do While Not EOF(1)
        Input #1, wordlist(x)
        x = x + 1
    Loop
   
    Close #1
   
    Range("B2").Select
    ' Set Do loop to stop when an empty cell is reached.
    Do Until IsEmpty(ActiveCell)
        For i = 1 To size
            If InStrB(1, ActiveCell.Value, wordlist(i), vbBinaryCompare) <> 0 Then
                ActiveCell.EntireRow.Interior.ColorIndex = 36
            End If
        Next i
        ActiveCell.Offset(1, 0).Select
    Loop

End Sub

 

[earthworm]

trying changing Integer as string

 

[Peter_SSs]

Can you please help me find out why the script show this error?

Which line shows that error?

trying changing Integer as string

Why? Both variables declared as Integer are clearly numeric.

 

[Alex Blakenburg]

The only way I can get the code to fail is to include a comma in the text file.
It seems to treat the comma as a line feed.
It then fails on:
Input #1, wordlist(x)

@thilooter
- as previously requested by Peter can you confirm if that is the line your code is erroring out on and if not which line ?
- do you have any commas in the text file and if so should they be there ?
I don't know much about Mac OS which may be a factor as well.

PS:
"i" is not declared, should be Dim i As Integer but this is not causing the error.

 

[thilooter]

Hi,

Thanks for the help so far.

It appears to error out on this line "9 str = Input(LOF(1), 1)"

There are no commas in the txt file. Sample of txt below:

"5-min"
"123"
"Troom"
"Ratata"
"Panda"
.....

Yeah thing is that this ran fine on windows but no go on mac :/

 

[Peter_SSs]

but no go on mac

Sorry, I do not use a Mac so am unable to advise regarding that.

 

[Alex Blakenburg]

@thilooter
I am in the same boat and it does seem to be specifically a Mac issue.
The only thing I could suggest is if you comment out the code that determines the size and replace it with a hardcoded number that you think will exceed the actual size and see if the next loop suffers the same issue.

eg

'Get Length of List - commented out
'Open textFile For Input As #1
'str = Input(LOF(1), 1)
'size = Len(str) - Len(Replace(str, vbLf, "")) + 1
'Close #1

'Set a size manually
size = 20

 

[earthworm]

Which line shows that error?


Why? Both variables declared as Integer are clearly numeric.

Agreed . But based on my experience sometimes when the data is too big , the numeric variable doesn't store large data in memory.
I have faced this issue in my macros as well so I changed it to string and it worked. I think variant variable also works if data is huge to be stored in memory

 

[Alex Blakenburg]

@Ron de Bruin has a site specifically covering the Mac OS but I couldn't fine anything addressing your issue.
Since you are not erroring out until after the line "Open textFile For Input As #1", I imagine your file name is fine which seems to be one of the most common issues.
His page relating to files and folders is:-
Files/Folders | Mac Excel Automation